1
00:00:00,120 --> 00:00:00,450
All right.

2
00:00:00,450 --> 00:00:02,850
Next, we're going to talk about default values.

3
00:00:02,850 --> 00:00:05,990
As we saw when I describe any of our tables so far.

4
00:00:06,000 --> 00:00:07,200
Describe cats.

5
00:00:07,230 --> 00:00:08,430
Cats, too.

6
00:00:09,090 --> 00:00:14,940
We have this default column here that describes the default values present for a column.

7
00:00:15,090 --> 00:00:19,800
Kind of a confusing sentence, but essentially there is no default value for name or age.

8
00:00:19,830 --> 00:00:21,270
Same thing with cats too.

9
00:00:21,300 --> 00:00:23,970
There's no default value, but we can change that.

10
00:00:23,970 --> 00:00:31,320
When we create a table, we can specify a default value using this syntax default and then immediately

11
00:00:31,320 --> 00:00:37,650
after separated by a space, whatever the default value is that we want to set now, we can alter a

12
00:00:37,650 --> 00:00:40,650
table as well to do this after the fact, but we haven't covered that yet.

13
00:00:40,650 --> 00:00:44,010
So once again, we're going to make a new table this time.

14
00:00:44,010 --> 00:00:45,060
Katz three.

15
00:00:45,090 --> 00:00:46,480
It's going to get a little confusing.

16
00:00:46,500 --> 00:00:47,370
Cats, Cats, two.

17
00:00:47,400 --> 00:00:47,880
Cats three.

18
00:00:47,880 --> 00:00:51,540
But we've only got one or two more, so let's do cats three.

19
00:00:52,020 --> 00:00:54,480
And I'm going to just keep it simple again.

20
00:00:54,480 --> 00:00:55,760
Name of our car.

21
00:00:55,770 --> 00:01:02,610
We'll have a default of unnamed or no name or unknown, whatever it would make sense to set up as a

22
00:01:02,610 --> 00:01:04,110
default value for a cat.

23
00:01:04,110 --> 00:01:07,440
Let's say we're running a shelter and we bring in cats off the street.

24
00:01:07,440 --> 00:01:09,060
They don't have microchips or collars.

25
00:01:09,060 --> 00:01:10,110
We don't know their name.

26
00:01:10,110 --> 00:01:12,240
We probably still assign them some name.

27
00:01:12,510 --> 00:01:17,490
I think the way shelters usually do it is they actually have a list of different random names they can

28
00:01:17,490 --> 00:01:22,320
pick from instead of naming every cat that comes in, you know, John Doe or something.

29
00:01:22,320 --> 00:01:23,730
But it doesn't matter.

30
00:01:23,760 --> 00:01:25,350
That's not the point of this course.

31
00:01:25,650 --> 00:01:28,590
But let's do a create table Cats three.

32
00:01:29,560 --> 00:01:35,740
And we'll have name, which again will be our car and the slides that did 100, It doesn't really matter.

33
00:01:35,770 --> 00:01:39,380
We're going to have short cat names, so I'll do 50 and then default.

34
00:01:39,400 --> 00:01:47,040
Let's go with mystery and then age is an int.

35
00:01:47,050 --> 00:01:52,180
We don't have to provide a default value if we don't want to, but in this case just to demonstrate

36
00:01:52,180 --> 00:01:57,790
it, I will age int the default value will be 99, just like the slide.

37
00:01:57,820 --> 00:02:02,380
All right, close my Perin semicolon and we're done.

38
00:02:02,530 --> 00:02:06,610
So now what I can do is insert into cat three.

39
00:02:06,880 --> 00:02:13,390
Let's say I only provide an age because this cat that we just picked up off the street, this poor wet,

40
00:02:13,390 --> 00:02:17,470
sad cat, we don't know its name so we can estimate its age.

41
00:02:17,470 --> 00:02:19,660
I guess we'll say it's age is two.

42
00:02:19,690 --> 00:02:24,400
If we look at cats three, select star from Cats three.

43
00:02:24,430 --> 00:02:25,150
We'll see.

44
00:02:25,150 --> 00:02:27,370
That name has been set to mystery.

45
00:02:27,730 --> 00:02:29,470
So that default value kicked in.

46
00:02:29,470 --> 00:02:35,050
In fact, I could insert a cat that has nothing when we create it.

47
00:02:35,050 --> 00:02:36,940
No value is present.

48
00:02:36,940 --> 00:02:44,470
But because we have those default values present, we end up with mystery and 99.

49
00:02:44,470 --> 00:02:46,960
Now it's kind of confusing to have all these cats called mystery.

50
00:02:46,960 --> 00:02:49,630
And why on earth would the default age be 99?

51
00:02:49,900 --> 00:02:50,770
It doesn't matter.

52
00:02:50,860 --> 00:02:52,030
Stop asking questions.

53
00:02:52,030 --> 00:02:55,900
So that's how we set up a default value, at least when we create a table.

54
00:02:55,900 --> 00:02:57,760
That's how we set up a default value.

55
00:02:57,790 --> 00:02:59,620
Following the name of the column.

56
00:02:59,620 --> 00:03:06,490
We then specify the data type and then add ons like not null or in this case default followed by the

57
00:03:06,490 --> 00:03:07,450
default value.

58
00:03:07,750 --> 00:03:13,840
Now, something that you might be wondering is do we need to specify not null and default?

59
00:03:13,840 --> 00:03:20,710
Because by providing a default value, doesn't that mean that there's no way for this value to be null?

60
00:03:20,740 --> 00:03:21,160
Right?

61
00:03:21,160 --> 00:03:24,940
If I inserted this empty cat as we saw right here.

62
00:03:26,280 --> 00:03:29,070
And we select Star again from Catch three.

63
00:03:29,370 --> 00:03:33,390
We don't end up with null values because we have default values.

64
00:03:33,420 --> 00:03:40,380
However, that doesn't stop me from changing this to be null or changing this to be null.

65
00:03:40,440 --> 00:03:46,650
Just setting a default value is not the same as saying this can never be null, so they can work in

66
00:03:46,650 --> 00:03:48,120
tandem, not null.

67
00:03:48,120 --> 00:03:49,890
Just says this can never be empty.

68
00:03:49,890 --> 00:03:52,920
This particular column, this value can be empty.

69
00:03:53,040 --> 00:03:59,670
And default is simply saying if there is no value specified, it should default to this value.

70
00:03:59,820 --> 00:04:07,650
But if we had default without not null, then I could easily set a value to be null manually.

71
00:04:07,650 --> 00:04:09,390
So I can do that right here.

72
00:04:09,390 --> 00:04:10,020
I'll show you.

73
00:04:10,020 --> 00:04:14,130
I can insert the value null directly into a table.

74
00:04:14,130 --> 00:04:16,410
So insert in two cats.

75
00:04:16,410 --> 00:04:17,310
Three.

76
00:04:17,820 --> 00:04:22,980
Let's say name and age values will be null and no.

77
00:04:22,980 --> 00:04:25,260
So it's a special my SQL value.

78
00:04:25,650 --> 00:04:27,300
And then I'll select all my cats.

79
00:04:27,300 --> 00:04:28,380
And what do we see?

80
00:04:28,380 --> 00:04:30,330
No and no.

81
00:04:30,540 --> 00:04:34,980
So that tells us that a default value is not going to protect us in a situation like that.

82
00:04:34,980 --> 00:04:41,340
If I manually set something to NULL, which is not always a bad thing, sometimes we want to allow NULL.

83
00:04:41,340 --> 00:04:48,990
Imagine we're setting up a a phone book or a contact app or something where users have to have a or

84
00:04:48,990 --> 00:04:55,590
every row in the user's table has a name, an email, a cell phone, and maybe a home phone number and

85
00:04:55,620 --> 00:04:57,120
maybe a fax number.

86
00:04:57,120 --> 00:04:59,130
But not everybody has those.

87
00:04:59,130 --> 00:05:05,640
So maybe we make cell phone a requirement or home phone a requirement, but we can make the others knowable.

88
00:05:05,640 --> 00:05:07,290
They don't have to be present.

89
00:05:07,290 --> 00:05:10,920
So we could say your fax number can be null if you don't have a fax machine.

90
00:05:10,920 --> 00:05:11,760
I don't have one.

91
00:05:11,760 --> 00:05:13,500
We would allow that to be null.

92
00:05:13,500 --> 00:05:19,350
So null is not a bad thing, but it can be a bad thing if you aren't expecting it or you don't want

93
00:05:19,350 --> 00:05:20,400
to allow it.

94
00:05:20,400 --> 00:05:26,040
But all you have to do to make sure that a value is not knowable and has a default value if it's not

95
00:05:26,040 --> 00:05:30,480
present, is to use both not null followed by default.

96
00:05:30,930 --> 00:05:35,850
So to save time, I'll just copy and paste this so that you don't have to watch me type the whole thing.

97
00:05:35,880 --> 00:05:37,050
I'll paste it in there.

98
00:05:37,050 --> 00:05:43,980
And now if I were to try to insert into Cats four where I manually write null as my two values, I'll

99
00:05:43,980 --> 00:05:44,520
get an error.

100
00:05:44,520 --> 00:05:50,520
It says name cannot be null and it would have said age cannot be null, but it stopped at that first

101
00:05:50,520 --> 00:05:52,800
error so it didn't insert anything.

102
00:05:53,220 --> 00:05:58,920
But if I instead did this insert into cats for empty empty.

103
00:05:59,710 --> 00:06:01,750
We will get those default values.

104
00:06:01,750 --> 00:06:03,040
So I know this is kind of boring.

105
00:06:03,040 --> 00:06:07,060
I hope I was at least clear here so you understand the difference.

106
00:06:07,150 --> 00:06:08,220
Cats forward.

107
00:06:08,230 --> 00:06:10,610
We have unnamed and 99.

108
00:06:10,630 --> 00:06:12,280
Those were the default values.

109
00:06:12,280 --> 00:06:19,990
And if I go to a describe cats for not only do we see that it's not notable name and age, neither of

110
00:06:19,990 --> 00:06:23,080
which are knowable, we also see the default values listed.

111
00:06:23,080 --> 00:06:28,570
So if you ever forget what the default value is, you can describe your table and my SQL will show you

112
00:06:28,570 --> 00:06:29,200
right there.

113
00:06:30,190 --> 00:06:31,270
I hope you're hanging in there.