1
00:00:00,240 --> 00:00:00,860
Okay.

2
00:00:01,200 --> 00:00:03,750
As you can see, the slide says we have one more thing.

3
00:00:03,750 --> 00:00:04,920
And that is true.

4
00:00:04,950 --> 00:00:09,030
We have one more thing to cover here before we do our final exercise in this section.

5
00:00:10,080 --> 00:00:17,430
So in keeping with this pattern, what's up with this this key column here and it's empty.

6
00:00:17,430 --> 00:00:19,290
There's nothing for name or age.

7
00:00:19,320 --> 00:00:20,370
What does this mean?

8
00:00:20,880 --> 00:00:26,430
So to dive into this and this is actually something we are going to come back to quite a ways down the

9
00:00:26,430 --> 00:00:30,870
line in this course, but we'll experience working with Key for the first time in this video.

10
00:00:32,159 --> 00:00:36,180
So to illustrate the problem right now, I could do this.

11
00:00:36,180 --> 00:00:39,150
I could insert identical data.

12
00:00:39,210 --> 00:00:43,350
Manti Te'o, Manti Te'o, Manti Te'o, Manti Te'o and so on.

13
00:00:43,350 --> 00:00:44,970
And we've already seen this.

14
00:00:44,970 --> 00:00:52,530
But just to prove that to you, if I just work with cats, let's do select star from Cats.

15
00:00:52,530 --> 00:00:55,260
And this is true about any of the cats tables we have.

16
00:00:55,920 --> 00:00:59,130
You can actually already see we have two cats that are identical.

17
00:00:59,760 --> 00:01:01,050
No and no.

18
00:01:01,200 --> 00:01:09,300
But I could also do something like insert into cats name, age, values.

19
00:01:10,080 --> 00:01:12,870
And this one will be called Helena.

20
00:01:14,430 --> 00:01:19,350
And Helena will be six or Helena, however you say it.

21
00:01:20,370 --> 00:01:23,700
We can do this and we can do it as many times as you want.

22
00:01:23,700 --> 00:01:26,730
And there's going to be identical data in there.

23
00:01:28,780 --> 00:01:32,290
As you can see, we have five identical cats.

24
00:01:32,290 --> 00:01:33,550
So why should you care?

25
00:01:33,670 --> 00:01:35,260
Why is this really a problem?

26
00:01:35,710 --> 00:01:41,590
And there's a couple of reasons, but it comes down to the fact that we want all of our data to be uniquely

27
00:01:41,590 --> 00:01:45,580
identifiable, even if it looks the same like it does in this case.

28
00:01:46,600 --> 00:01:48,820
We assume that these are separate cats.

29
00:01:48,910 --> 00:01:52,120
If they are in fact one cat, then this is just an error.

30
00:01:52,120 --> 00:01:58,540
We don't want to have this many entries, but imagine we actually have five cats named Elena with an

31
00:01:58,540 --> 00:01:59,380
age of six.

32
00:01:59,710 --> 00:02:03,280
They are unique and individual and we want to be able to keep track of them.

33
00:02:03,280 --> 00:02:04,720
And right now we can't.

34
00:02:04,720 --> 00:02:07,870
There is no way to uniquely refer to this.

35
00:02:08,919 --> 00:02:16,510
So in a case like here where we have Alabama, who is null, age null and then we have null, who is

36
00:02:16,510 --> 00:02:22,150
age null, there is a way of telling these apart even though they both have an age of null.

37
00:02:22,420 --> 00:02:28,720
I could tell SQL and we don't know how to do this yet, but I could say Give me a cat whose age is null

38
00:02:28,720 --> 00:02:32,320
and whose name is Alabama, and that can get me this.

39
00:02:32,980 --> 00:02:38,080
But there's no way for me to get this cat and separate it from this cat or this one and separate it

40
00:02:38,080 --> 00:02:38,740
from this one.

41
00:02:38,740 --> 00:02:41,170
And that's really a useful and important thing.

42
00:02:41,170 --> 00:02:47,020
And it's actually a really fundamental part of working with databases, is having things be identifiable

43
00:02:47,470 --> 00:02:53,530
when you're signing up for an account on, let's say, Twitter and you're trying to make a username,

44
00:02:53,680 --> 00:02:58,390
there is a reason that you can't just make the same username that already exists.

45
00:02:58,780 --> 00:03:03,670
I wish that I could just go and make my username Colt, but somebody already has that.

46
00:03:03,670 --> 00:03:10,180
And it's not just about keeping it simple and not being confusing for users, because obviously it would

47
00:03:10,180 --> 00:03:15,760
be confusing if there was one colt and then another colt and they're both tweeting, how do you know

48
00:03:15,760 --> 00:03:16,570
who is who?

49
00:03:16,750 --> 00:03:23,770
But actually in the code, that's hugely problematic because there's things like when I log in, I type

50
00:03:23,770 --> 00:03:30,070
my username and it's going to go to the database and try and find a user with username colt and then

51
00:03:30,070 --> 00:03:31,420
it's going to match my password.

52
00:03:31,420 --> 00:03:34,960
But if there's two users with the username Colt, then what does it do?

53
00:03:35,380 --> 00:03:39,640
So in that case, you're not allowed to have separate usernames.

54
00:03:39,880 --> 00:03:43,600
But there are times when we have data that looks the same.

55
00:03:44,300 --> 00:03:51,000
Like hypothetically, we could have two cats named Helena, age six, or maybe a better example.

56
00:03:51,020 --> 00:03:54,560
We could have a couple of cats if we go back.

57
00:03:56,060 --> 00:04:02,120
Here where we have no name provided they're unnamed, but they are unique, different cats.

58
00:04:02,390 --> 00:04:03,650
How do we know?

59
00:04:04,700 --> 00:04:13,460
And the easiest way and what we'll do and what pretty much everyone does is assign an ID, a unique

60
00:04:13,460 --> 00:04:14,420
identifier.

61
00:04:15,900 --> 00:04:22,440
So you can see here I added a new field cat ID and every cat is given one of these IDs.

62
00:04:22,830 --> 00:04:29,310
So now, rather than saying I want to select Monty from this database and getting all of these, I can

63
00:04:29,310 --> 00:04:35,130
say I want the cat with ID one or ID three and they are uniquely identifiable.

64
00:04:35,160 --> 00:04:35,670
All right.

65
00:04:35,670 --> 00:04:39,960
So there's a precise term for this primary key.

66
00:04:39,990 --> 00:04:47,160
So a primary key just refers to something that is a unique identifier on a row.

67
00:04:47,190 --> 00:04:49,230
It's a way of identifying something.

68
00:04:49,230 --> 00:04:51,690
And you know that it will be unique.

69
00:04:51,720 --> 00:04:52,710
Just a heads up.

70
00:04:52,710 --> 00:04:55,650
We will see another type of key later on called foreign key.

71
00:04:55,800 --> 00:04:57,030
Those are both keys.

72
00:04:57,270 --> 00:04:57,960
Don't worry about it.

73
00:04:57,960 --> 00:05:04,230
But primary key just means that whatever field we assign, whatever we make the primary key.

74
00:05:04,500 --> 00:05:06,990
So in this case, we would make cat ID.

75
00:05:07,020 --> 00:05:10,140
A primary key means that it has to be unique.

76
00:05:10,230 --> 00:05:12,780
So let's see how we actually assign one.

77
00:05:13,050 --> 00:05:18,510
So I'm creating a new table here, and I know it's a little bit longer than what we've seen, but a

78
00:05:18,510 --> 00:05:20,030
lot of it is very, very similar.

79
00:05:20,040 --> 00:05:21,350
So we have unique cats.

80
00:05:21,360 --> 00:05:22,530
It's just the name of the table.

81
00:05:22,530 --> 00:05:24,000
Just I got tired of cats.

82
00:05:24,000 --> 00:05:25,170
One, two, three, four.

83
00:05:25,200 --> 00:05:26,430
So unique cats.

84
00:05:26,940 --> 00:05:31,950
We have name and age, but we also have cat ID now.

85
00:05:32,250 --> 00:05:37,230
So cat ID is an integer and we can't have cat ID B?

86
00:05:37,230 --> 00:05:40,440
No, we definitely want there to be something there.

87
00:05:41,490 --> 00:05:44,910
And then this is the new and important piece.

88
00:05:45,270 --> 00:05:46,740
This is just another field.

89
00:05:46,740 --> 00:05:52,230
This isn't technically new conceptually, but this is primary key.

90
00:05:52,230 --> 00:05:54,390
And then we give it the name of a field.

91
00:05:55,280 --> 00:05:57,870
So we want the cat ID to be unique.

92
00:05:57,890 --> 00:06:01,580
We want it to be the unique identifier, so we just pass it in here.

93
00:06:02,060 --> 00:06:02,900
Cat ID.

94
00:06:03,530 --> 00:06:04,820
So let's try this out.

95
00:06:05,150 --> 00:06:09,320
I'm just going to copy it over just to save you time so you don't have to watch me type the whole thing.

96
00:06:11,720 --> 00:06:12,470
Here we go.

97
00:06:12,500 --> 00:06:16,700
Create table unique cats, a cat ID name age.

98
00:06:16,700 --> 00:06:21,110
And by the way, that cat ID I talked about earlier is a primary key.

99
00:06:21,950 --> 00:06:26,690
All right, so now let's do describe unique cats.

100
00:06:28,630 --> 00:06:30,010
And you'll see a couple of things.

101
00:06:30,040 --> 00:06:38,590
We have cat ID now because we have this line here, but we also under this key column, we have primary

102
00:06:38,590 --> 00:06:41,610
or PRI, but it means primary key.

103
00:06:41,620 --> 00:06:43,890
So cat ID is a primary key.

104
00:06:43,900 --> 00:06:44,980
So how do we know?

105
00:06:45,550 --> 00:06:51,670
Well, let's insert into cats or unique cats.

106
00:06:53,470 --> 00:06:57,550
We'll do ID, name and age just like that.

107
00:06:58,490 --> 00:07:05,240
And then we'll do values and we'll start off with an idea of one name will be Fred.

108
00:07:07,500 --> 00:07:08,910
An age will be one.

109
00:07:09,090 --> 00:07:09,900
Let's do two.

110
00:07:09,930 --> 00:07:10,410
Fine.

111
00:07:10,410 --> 00:07:11,250
23.

112
00:07:11,430 --> 00:07:14,670
Except the typo OC hit enter.

113
00:07:15,630 --> 00:07:16,950
Oh, we have a problem.

114
00:07:17,220 --> 00:07:18,930
I just referred to ID.

115
00:07:18,960 --> 00:07:20,400
It should be cat ID.

116
00:07:21,090 --> 00:07:22,200
Silly mistake.

117
00:07:23,110 --> 00:07:25,210
So let's fix that cat ID.

118
00:07:27,130 --> 00:07:29,380
So now just double check our work.

119
00:07:33,050 --> 00:07:34,520
From Unique Cats.

120
00:07:35,510 --> 00:07:36,500
Cat ideas one.

121
00:07:36,680 --> 00:07:37,160
All right.

122
00:07:37,160 --> 00:07:39,080
So we could do the same thing.

123
00:07:40,580 --> 00:07:42,080
Change us to two.

124
00:07:42,260 --> 00:07:45,740
And instead of Fred, let's have Louise.

125
00:07:46,970 --> 00:07:47,960
Who is to.

126
00:07:49,390 --> 00:07:51,670
Three we enter.

127
00:07:52,180 --> 00:07:52,810
Same thing.

128
00:07:52,810 --> 00:07:54,460
I think you get where I'm going here.

129
00:07:54,460 --> 00:07:59,560
We can keep doing this, however, and this is where the primary key part comes in.

130
00:08:00,700 --> 00:08:04,900
I can now say insert into unique cats cat id name age.

131
00:08:05,440 --> 00:08:08,140
What if I tried to make another one with ID one?

132
00:08:08,650 --> 00:08:10,390
And this one is called.

133
00:08:11,470 --> 00:08:12,220
James.

134
00:08:13,270 --> 00:08:14,200
Just like that.

135
00:08:16,030 --> 00:08:20,140
I get a new error duplicate entry one four key primary.

136
00:08:20,440 --> 00:08:23,170
So this is saying, wait a second, you said it's a primary key.

137
00:08:23,170 --> 00:08:25,030
I cannot have a duplicate entry.

138
00:08:25,030 --> 00:08:26,360
You cannot have something.

139
00:08:26,380 --> 00:08:29,720
The whole point is that ID is supposed to be uniquely identifiable.

140
00:08:29,740 --> 00:08:31,120
Well, that's a problem.

141
00:08:31,870 --> 00:08:33,570
So it doesn't add it to the database.

142
00:08:33,580 --> 00:08:35,140
So that's really, really important.

143
00:08:35,530 --> 00:08:37,929
And IDs are not the only thing you can make.

144
00:08:37,929 --> 00:08:38,830
Primary keys.

145
00:08:38,860 --> 00:08:42,789
I gave that example of username, which is something we'll come back to later.

146
00:08:43,809 --> 00:08:49,420
You often want to make a username, a primary key, so that you can't have more than one account with

147
00:08:49,420 --> 00:08:51,280
the same username and your database.

148
00:08:51,700 --> 00:08:58,270
And so all that you would do is instead of saying primary key, where is that cat ID?

149
00:08:58,450 --> 00:09:02,050
You would say primary key username or whatever the name of the field is.

150
00:09:02,050 --> 00:09:07,180
And then if I tried to insert something with an already existing username, I would get the same error

151
00:09:07,180 --> 00:09:07,600
here.

152
00:09:09,370 --> 00:09:15,340
Okay, one more thing to talk about, which is it's kind of annoying to have to manually specify this

153
00:09:15,340 --> 00:09:15,970
id.

154
00:09:16,120 --> 00:09:17,110
It's more than annoying.

155
00:09:17,110 --> 00:09:18,060
It's impractical.

156
00:09:18,070 --> 00:09:20,620
I mean, are you just supposed to know where you left off?

157
00:09:21,010 --> 00:09:22,000
One, two, three.

158
00:09:22,000 --> 00:09:25,270
And every time you insert a cat, you just have to know what the last one was.

159
00:09:25,990 --> 00:09:27,580
It just doesn't really work.

160
00:09:27,610 --> 00:09:32,800
It's not even in this shell context, but especially not in a real application.

161
00:09:33,130 --> 00:09:33,820
You can't.

162
00:09:33,970 --> 00:09:34,780
It's just a pain.

163
00:09:35,140 --> 00:09:37,210
So there's a way around it.

164
00:09:37,900 --> 00:09:38,830
Our code gets longer.

165
00:09:38,830 --> 00:09:43,390
I know, but all that's added here is this auto increment.

166
00:09:44,400 --> 00:09:45,870
So it does what it sounds like.

167
00:09:46,500 --> 00:09:52,990
It will take cat ID and it will automatically add one to it every time a new cat is created.

168
00:09:53,010 --> 00:09:57,780
So we no longer have to specify cat ID is one, cat ID is two.

169
00:09:58,080 --> 00:09:59,030
It will just do it.

170
00:09:59,040 --> 00:10:03,270
We just leave off cat ID entirely and everything else here is the same.

171
00:10:04,080 --> 00:10:05,400
So cat unique.

172
00:10:05,400 --> 00:10:14,010
Cat two I'm just going to copy this over, make some space hit enter and before I show you that it works.

173
00:10:14,010 --> 00:10:22,530
If we do describe Unique Cat two, you'll notice that we also have auto whoops.

174
00:10:22,530 --> 00:10:28,650
We have auto increment here under extra so it doesn't get its own field, but it's considered extra.

175
00:10:29,010 --> 00:10:40,230
So now let's try and insert something into that insert into unique cat two and we'll do name and age.

176
00:10:40,230 --> 00:10:45,840
We don't specify cat ID anymore values.

177
00:10:47,580 --> 00:10:51,300
And let's see, seriously, the hardest part of all of this.

178
00:10:51,300 --> 00:10:52,770
Let's go with Skippy.

179
00:10:52,890 --> 00:10:58,020
Once again, I see some peanut butter in my kitchen and age will be four.

180
00:10:58,800 --> 00:10:59,820
Just like that.

181
00:11:01,560 --> 00:11:11,220
Now if I do select star from unique cats to cat, it is automatically one.

182
00:11:11,850 --> 00:11:17,940
And to prove to you that its auto incrementing lets change this name to Jif.

183
00:11:20,720 --> 00:11:22,250
And we select Star again.

184
00:11:23,060 --> 00:11:24,500
And now we have two.

185
00:11:25,700 --> 00:11:26,990
So this solves two problems.

186
00:11:26,990 --> 00:11:32,360
One is that it's annoying to have to manually type those IDs and keep track of what number we're on

187
00:11:32,360 --> 00:11:32,960
next.

188
00:11:33,170 --> 00:11:39,650
But also, I can now have duplicate cats as far as name and age, which is our whole goal from the get

189
00:11:39,650 --> 00:11:40,060
go.

190
00:11:40,070 --> 00:11:46,640
So I can now do another Jif and another Jif again.

191
00:11:46,730 --> 00:11:48,860
And this time let's do a Skippy as well.

192
00:11:49,550 --> 00:11:54,710
Now select star from Unique Cats two.

193
00:11:55,580 --> 00:11:59,870
And you can see we have Skippy here who's four and Skippy here is four.

194
00:11:59,870 --> 00:12:01,580
But they have different IDs.

195
00:12:01,580 --> 00:12:03,620
Therefore they are unique.

196
00:12:04,130 --> 00:12:05,870
Same thing with three gifs.

197
00:12:06,200 --> 00:12:06,840
Perfect.

198
00:12:06,860 --> 00:12:07,460
Alrighty.

199
00:12:07,460 --> 00:12:10,460
So that concludes all the new stuff in this section.

200
00:12:11,090 --> 00:12:15,860
Now you're going to get a chance to put an action to actually practice it and to write your own bit

201
00:12:15,860 --> 00:12:16,660
of code.

202
00:12:16,670 --> 00:12:20,060
Create your own table that looks a little bit something like this.

203
00:12:20,630 --> 00:12:21,060
Okay.

