WEBVTT 00:00.270 --> 00:04.960 OK as you can see the slide says we have one more thing and that is true. 00:04.980 --> 00:09.510 We have one more thing to cover here before we do our final exercise in this section. 00:10.200 --> 00:17.420 So in keeping with this pattern What's up with this this key column here and it's empty. 00:17.430 --> 00:19.410 There's nothing for a name or age. 00:19.440 --> 00:20.830 What does this mean. 00:20.870 --> 00:26.400 So to dive into this and this is actually something we're going to come back to quite a ways down the 00:26.400 --> 00:27.390 line in this course. 00:27.480 --> 00:30.800 But we'll experience working with Keith for the first time in this video. 00:32.210 --> 00:41.090 So to illustrate the problem right now I could do this I could insert identical data Montie 10 Montie 00:41.090 --> 00:43.290 10 Montie 10 Montie 10 and so on. 00:43.460 --> 00:44.950 And we've already seen this. 00:44.960 --> 00:52.970 But just to prove that to you if I just worked with cats let's do select start from cats and this is 00:52.970 --> 00:55.550 true about any of the cats tables we have. 00:56.060 --> 00:59.810 You can actually already see we have two cats that are identical. 00:59.810 --> 01:01.160 No and No. 01:01.310 --> 01:12.650 But I could also do something like insert into cat's name age values and this one will be called Helena 01:14.510 --> 01:23.780 and I will be six or Halina you say it we can do this and we can do it as many times as you want and 01:23.780 --> 01:32.300 there's going to be identical data and there as you can see we have five identical cats. 01:32.470 --> 01:33.610 So why should you care. 01:33.760 --> 01:35.420 Why is this really a problem. 01:35.890 --> 01:41.530 And there's a couple of reasons but it comes down to the fact that we want all of our data to be uniquely 01:41.620 --> 01:46.750 identifiable even if it looks the same like it does in this case. 01:46.750 --> 01:52.110 We assume that these are separate cats if they are in fact one cat then this is just an error. 01:52.120 --> 01:58.690 We don't want to have this many entries but imagine we actually have five cats named Tilla with an age 01:58.690 --> 01:59.790 of six. 01:59.830 --> 02:01.560 They are unique and individual. 02:01.570 --> 02:03.250 And we want to be able to keep track of them. 02:03.250 --> 02:04.840 And right now we can't. 02:04.840 --> 02:08.430 There is no way to uniquely refer to this. 02:09.070 --> 02:14.590 So in a case like here where we have Alabama who has no age no. 02:14.650 --> 02:16.830 And then we have know who is age. 02:16.830 --> 02:23.650 No there is a way of telling these apart even though they both have age of gnoll I could tell sequel 02:23.650 --> 02:29.440 and we don't know how to do this yet but I could say give me a cat whose age is nil and whose name is 02:29.440 --> 02:32.720 Alabama and that can get me this. 02:33.070 --> 02:38.380 But there's no way for me to get this cat separated from this cat or this one and separated from this 02:38.380 --> 02:38.800 one. 02:38.860 --> 02:43.810 And that's really a useful and important thing and it's actually a really fundamental part of working 02:43.810 --> 02:51.310 with databases is having things be identifiable when you're signing up for an account on let's say Twitter 02:51.910 --> 02:53.850 and you're trying to make a user name. 02:53.900 --> 02:58.550 There's a reason that you can't just make the same username that already exists. 02:58.900 --> 03:04.840 I wish that I could just go and make my username cult but somebody already has that and it's not just 03:04.840 --> 03:10.930 about keeping it simple and not being confusing for users because obviously it would be confusing if 03:10.930 --> 03:15.280 there was one cold and then another cold and they're both tweeting. 03:15.280 --> 03:16.600 How do you know who is who. 03:16.900 --> 03:23.740 But actually in the code that's hugely problematic because there's things like when I log in I typed 03:23.740 --> 03:30.040 my username and it's going to go to the database and try and find a user with username cult and then 03:30.040 --> 03:31.470 it's going to match my password. 03:31.480 --> 03:35.500 But if there's two users with username colt then what does it do. 03:35.530 --> 03:42.190 So in that case you're not allowed to have separate usernames but there are times when we have data 03:42.190 --> 03:50.350 that looks the same like hypothetically we could have two cats named Helena age six or maybe a better 03:50.350 --> 03:59.530 example we could have a couple of cats if we go back here where we have no name provided they're unnamed 04:00.130 --> 04:00.970 but they're unique. 04:00.970 --> 04:02.200 Different cats. 04:02.530 --> 04:04.050 How do we know. 04:04.810 --> 04:13.450 And the easiest way and what we'll do and what pretty much everyone does is assign an I.D. a unique 04:13.450 --> 04:22.870 identifier so you can see here it added a new field cat I.D. and every cat is given one of these IDs. 04:22.960 --> 04:29.260 So now rather than saying I want to select Montie from this database and getting all of these I can 04:29.260 --> 04:33.160 say I want the cat with I.D. one or ID three. 04:33.340 --> 04:35.200 And they are uniquely identifiable. 04:35.200 --> 04:35.830 All right. 04:35.830 --> 04:39.980 So there is a precise term for this primary key. 04:40.180 --> 04:47.100 So a primary key just refers to something that is a unique identifier on a row. 04:47.200 --> 04:52.740 It's a way of identifying something and you know that it will be unique Just a heads up. 04:52.750 --> 04:55.860 We will see another type of key later on called foreign key. 04:55.870 --> 04:57.220 Those are both keys. 04:57.370 --> 04:57.940 Don't worry about it. 04:57.950 --> 05:04.320 The primary key just means that whatever field we assign whatever we make the primary key. 05:04.660 --> 05:10.310 So in this case we had made cat ID a primary key means that it has to be unique. 05:10.420 --> 05:13.110 So let's see how we actually assign one. 05:13.210 --> 05:18.640 So I'm creating a new table here and I know it's a little bit longer than what we've seen but a lot 05:18.640 --> 05:20.000 of it is very very similar. 05:20.200 --> 05:21.340 So we have unique cats. 05:21.370 --> 05:25.110 It's just the name of the table just I got tired of cats one two three four. 05:25.390 --> 05:32.030 So you need cats we have name and age but we also have cat I.D. now. 05:32.440 --> 05:37.870 So Cat I.D. is an integer and we can't have cat Id know. 05:38.320 --> 05:40.850 We definitely want there to be something there. 05:41.650 --> 05:45.200 And then this is the new and important piece. 05:45.400 --> 05:52.220 This is just another field this isn't technically new conceptually but this is primary key. 05:52.330 --> 05:55.300 And then we give it the name of a field. 05:55.370 --> 05:57.890 So we want the cat to be unique. 05:58.070 --> 06:02.990 We want it to be the unique identifier so we just pass it in here cat. 06:03.650 --> 06:05.200 So let's try this up. 06:05.330 --> 06:07.590 I'm just going to copy it over just to save you time. 06:07.610 --> 06:11.700 You don't have to watch me type thing. 06:11.750 --> 06:12.530 Here we go. 06:12.620 --> 06:14.380 Create table cats. 06:14.660 --> 06:16.890 Cat ID name age. 06:16.910 --> 06:21.980 And by the way that cat Id talked about earlier is primary key. 06:21.980 --> 06:30.090 All right so now let's do describe unique cats you'll see a couple of things. 06:30.220 --> 06:38.500 We have cat deal now because we had this line here but we also under this key call them we have primary 06:38.500 --> 06:41.660 here PRI but it means primary key. 06:41.680 --> 06:43.830 So Cat ID is a primary key. 06:44.050 --> 06:45.210 So how do we know. 06:45.700 --> 06:59.080 Well let's insert into cats or unique cats will do id name and age just like that and then we'll do 06:59.080 --> 07:09.620 values and we'll start off with an idea of one name will be Fred and age will be one that's used to 07:10.060 --> 07:11.240 find 23. 07:11.430 --> 07:12.480 Except the typo. 07:12.690 --> 07:15.210 Oh get it enter. 07:15.660 --> 07:17.370 Oh we have a problem. 07:17.370 --> 07:18.970 I just referred to ID. 07:18.980 --> 07:21.020 It should be cat ID. 07:21.190 --> 07:22.140 Silly mistake. 07:23.170 --> 07:25.710 So let's fix that cat ID. 07:26.430 --> 07:27.080 OK. 07:27.210 --> 07:29.550 Now just double check our work. 07:33.050 --> 07:36.720 From unique cats get ideas one. 07:36.740 --> 07:37.170 All right. 07:37.190 --> 07:39.730 So we could do the same thing. 07:40.470 --> 07:42.180 Change us to two. 07:42.470 --> 07:52.780 And instead of Fred let's have Louise who is two three we enter same thing. 07:52.820 --> 07:54.560 I think you get where I'm going here. 07:54.590 --> 08:00.330 We can keep doing this however and this is where the primary key part comes in. 08:00.800 --> 08:03.060 I can now say entered into unique cats. 08:03.140 --> 08:05.390 Cat ID name age. 08:05.570 --> 08:12.630 What if I tried to make another one with of one and this one is called James. 08:13.280 --> 08:20.250 Just like that I get a new error duplicate entry one for key primary. 08:20.570 --> 08:25.070 So this is saying wait a second you said as a primary key I cannot have a duplicate entry. 08:25.070 --> 08:26.390 You cannot have something. 08:26.450 --> 08:29.750 The whole point is that ID is supposed to be uniquely identifiable. 08:29.900 --> 08:31.850 Well that's a problem. 08:31.970 --> 08:33.740 So it doesn't added to the database. 08:33.740 --> 08:38.910 So that's really really important and IDs are not the only thing you can make primary keys. 08:38.930 --> 08:43.800 I gave that example of username which is something we'll come back to later. 08:43.880 --> 08:49.430 You often want to make a user name a primary key so that you can't have more than one account with the 08:49.430 --> 08:51.690 same username in your database. 08:51.810 --> 08:59.390 And so all that you would do is instead of saying Primary Key Where's that cat ID you would say a primary 08:59.390 --> 09:02.060 key username or whatever the name of the field is. 09:02.150 --> 09:07.160 And then if I tried to insert something with an already existing user name I would get the same error 09:07.160 --> 09:08.350 here. 09:09.360 --> 09:09.690 OK. 09:09.710 --> 09:16.270 One more thing to talk about which is it's kind of annoying to have to manually specify this ID. 09:16.310 --> 09:18.170 It's more than annoying it's impractical. 09:18.170 --> 09:23.360 I mean you just supposed to know where you left off one two three and every time you enter a cat you 09:23.360 --> 09:26.020 just have to know what the last one was. 09:26.060 --> 09:27.620 It just doesn't really work. 09:27.620 --> 09:32.980 It's not even in this shell context but especially not in a real application. 09:33.260 --> 09:34.910 You can't and it's just a pain. 09:35.270 --> 09:37.820 So there's a way around it. 09:37.940 --> 09:38.830 Our code gets longer. 09:38.840 --> 09:44.040 I know that all of that's added here is this auto increment. 09:44.420 --> 09:46.230 So it does what it sounds like. 09:46.550 --> 09:52.980 It will take cat ID and it will automatically add 1 to it every time a new cat is created. 09:53.210 --> 10:01.190 So we no longer have to specify cat ID is one cat ideas to it will just do it we just leave off Id entirely 10:01.790 --> 10:03.970 and everything else here is the same. 10:04.220 --> 10:06.400 So Cat unique cats too. 10:06.800 --> 10:11.670 I'm just going to copy this over make some space. 10:11.790 --> 10:13.990 Hit enter and before I show you that it works. 10:14.150 --> 10:22.520 If we do describe unique cats to notice that we also have auto Inc. 10:22.610 --> 10:29.110 We have autoincrement here under extra so it doesn't get its own field but is considered extra. 10:29.150 --> 10:36.880 So now let's try and insert something into that insert into unique caps too. 10:37.130 --> 10:40.340 And will you name and age. 10:40.370 --> 10:44.070 We don't specify cat ID anymore. 10:44.750 --> 10:49.490 Values and let's see. 10:49.490 --> 10:54.830 Seriously the hardest part of all of this let's go with the Skippy Once again I see some peanut butter 10:55.280 --> 10:56.110 in my kitchen. 10:56.320 --> 11:00.120 And age will be four just like that. 11:01.640 --> 11:11.380 Now if I do select star from unique cats to cat ID is automatically one. 11:11.980 --> 11:14.410 And to prove to you that it's auto incrementing. 11:14.570 --> 11:17.660 Let's change this name to Jeff 11:20.800 --> 11:23.170 and we like start again. 11:23.170 --> 11:25.010 And now we have two. 11:25.780 --> 11:26.980 So this solves two problems. 11:26.980 --> 11:32.320 One is that it's annoying to have to manually type those IDs and keep track of what number we're on 11:32.320 --> 11:33.150 next. 11:33.340 --> 11:40.120 But also I can now have duplicate cats as far as name and age which is our whole goal from the get go. 11:40.210 --> 11:49.610 So I can now do another Jeff and another Jeff again and this time let's do it skippy as well. 11:49.600 --> 11:59.430 Now select star from unique cat 2 and you could see we have Skippy here who's for it skippy here has 11:59.450 --> 12:01.610 four but they have different ideas. 12:01.720 --> 12:04.140 Therefore they are unique. 12:04.150 --> 12:07.470 Same thing with three fifths perfect already. 12:07.480 --> 12:11.110 So that concludes all the new stuff in this section. 12:11.140 --> 12:15.820 Now you're going to get a chance to put in action to actually practice it and to write your own bit 12:15.820 --> 12:16.580 of code. 12:16.780 --> 12:20.230 Create your own table that looks a little bit something like this. 12:20.590 --> 12:21.070 OK.