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.