1 00:00:00,420 --> 00:00:01,280 I'll keep this brief. 2 00:00:01,290 --> 00:00:05,250 We've got a little bit more with primary keys, as we saw in the previous video. 3 00:00:05,250 --> 00:00:10,470 When we define a table, when we create it, we can specify the column that we want to be a primary 4 00:00:10,500 --> 00:00:16,230 key by specifying primary key right after the column name and the data type and other constraints. 5 00:00:16,440 --> 00:00:22,080 But there's another way of doing this, another syntax that you'll see and it looks like this we can 6 00:00:22,080 --> 00:00:25,080 create our column Cat ID, it's an integer, it can't be null. 7 00:00:25,080 --> 00:00:31,390 And then later on we can come back and say, Hey, remember that cat ID make it a primary key. 8 00:00:31,410 --> 00:00:33,090 These will both do the same thing. 9 00:00:33,090 --> 00:00:34,650 It's just a different syntax. 10 00:00:34,650 --> 00:00:36,990 I actually tend to prefer this one here. 11 00:00:37,020 --> 00:00:44,490 However, this one is a little long for slides or horizontally long at least, so I'll often in the 12 00:00:44,490 --> 00:00:49,680 slides resort to this just to keep things a bit more compact so that I can make the text larger. 13 00:00:49,680 --> 00:00:51,420 But you'll see both of them in the world. 14 00:00:51,420 --> 00:00:53,040 You should know that they both exist. 15 00:00:53,040 --> 00:00:56,310 Primary key and then a column name. 16 00:00:56,310 --> 00:01:02,340 In our case, Cat ID is the same as calling it a primary key when we initially set it up. 17 00:01:02,340 --> 00:01:05,069 The next thing I want to talk about is not know. 18 00:01:05,489 --> 00:01:13,200 It is technically redundant to specify a column as a primary key and as not know because primary keys 19 00:01:13,200 --> 00:01:14,760 can never be null. 20 00:01:14,790 --> 00:01:16,230 That's just how they work. 21 00:01:16,230 --> 00:01:17,970 If I copy and paste this one. 22 00:01:18,510 --> 00:01:20,040 Well, it's called Unique Cats. 23 00:01:20,040 --> 00:01:21,780 I already have unique cats, don't I? 24 00:01:21,810 --> 00:01:23,340 Let's drop our table. 25 00:01:23,970 --> 00:01:24,900 Unique cats. 26 00:01:24,900 --> 00:01:27,900 Goodbye and remake it this time. 27 00:01:27,900 --> 00:01:29,700 Cat ID is simply an integer. 28 00:01:29,700 --> 00:01:33,150 It doesn't say not null, but I do make it a primary key. 29 00:01:33,240 --> 00:01:43,080 If I try and insert into unique cats and I do a cat ID, a name and an age, let's just start with a 30 00:01:43,080 --> 00:01:45,690 regular cat that meets those requirements. 31 00:01:45,690 --> 00:01:50,310 Cat ID is one name I'll just go to go this time. 32 00:01:51,030 --> 00:01:52,050 Age is two. 33 00:01:52,590 --> 00:01:54,390 I answered that cat, no problem. 34 00:01:54,600 --> 00:01:59,550 But let's say that I leave off this primary key cat ID. 35 00:02:00,030 --> 00:02:02,730 It doesn't say not, no, but it is a primary key. 36 00:02:02,730 --> 00:02:04,080 That means it's required. 37 00:02:04,080 --> 00:02:09,600 And if I hit enter, well, I get an error this time because I told SQL I was going to provide a cat 38 00:02:09,600 --> 00:02:10,740 ID and I didn't. 39 00:02:10,949 --> 00:02:12,660 So let me fix that. 40 00:02:12,780 --> 00:02:20,010 I'm not going to provide a cat ID as if I was expecting it to allow me to insert it without an ID. 41 00:02:20,190 --> 00:02:21,840 But we have a problem. 42 00:02:21,840 --> 00:02:27,390 It says Cat ID doesn't have a default value and that's the same error we saw when we set up not null, 43 00:02:27,390 --> 00:02:31,140 but we didn't set up, not know we set up a primary key. 44 00:02:31,170 --> 00:02:37,770 If I describe my cat's table though, or my unique cat's table, we do see that it cannot be null. 45 00:02:37,770 --> 00:02:39,270 So I didn't say that. 46 00:02:39,270 --> 00:02:40,980 I just said it was a primary key. 47 00:02:40,980 --> 00:02:47,100 But it's redundant to specify not null, because that primary key constraint requires that value to 48 00:02:47,100 --> 00:02:49,890 be present alongside being unique. 49 00:02:49,890 --> 00:02:54,510 The final thing and most useful thing that I'm going to show you, I think in this video is setting 50 00:02:54,510 --> 00:02:57,750 up auto increment on your primary key fields. 51 00:02:57,840 --> 00:03:01,140 So there's a special thing auto underscore increment. 52 00:03:01,140 --> 00:03:08,130 It's easy to forget the underscore because we have primary space key, but it is auto underscore increment 53 00:03:08,550 --> 00:03:12,810 is going to make a column automatically increment starting at one. 54 00:03:12,810 --> 00:03:15,330 You can change that, but it doesn't really matter for us. 55 00:03:15,330 --> 00:03:20,280 We'll have one and then two and then three and then four and we don't have to go and add anything. 56 00:03:20,280 --> 00:03:24,000 We don't have to manually insert and keep track of the unique IDs. 57 00:03:24,000 --> 00:03:26,820 We can just rely on the auto increment. 58 00:03:26,820 --> 00:03:31,320 So let's go ahead and copy this and paste it in. 59 00:03:32,050 --> 00:03:33,820 And it said also called unique cats. 60 00:03:33,850 --> 00:03:34,120 All right. 61 00:03:34,120 --> 00:03:34,930 This one is unique. 62 00:03:34,960 --> 00:03:37,660 Cats three terrible name. 63 00:03:38,380 --> 00:03:44,080 But if we do that, I can now insert into unique cats. 64 00:03:44,080 --> 00:03:50,590 Three And I don't have to specify a cat ID, I simply need name and age. 65 00:03:51,250 --> 00:03:55,170 And technically, I don't need any of those, actually, because they're not required. 66 00:03:55,180 --> 00:03:55,840 They could be. 67 00:03:55,840 --> 00:03:58,420 No, but let's add them in. 68 00:03:58,420 --> 00:04:03,460 So name will be Boingo and age will be one for this first cat. 69 00:04:04,060 --> 00:04:05,050 Let's take a look. 70 00:04:05,050 --> 00:04:13,600 Select start from unique cats three and we see that cat ID was automatically assigned and it's set to 71 00:04:13,600 --> 00:04:14,290 one. 72 00:04:14,650 --> 00:04:20,950 Now if I insert another cat, I can actually just insert the exact same cat in terms of name and age 73 00:04:20,950 --> 00:04:22,360 and keep doing that. 74 00:04:22,960 --> 00:04:29,700 And every single row that I insert here will have its own unique cat ID because I set up that auto increment. 75 00:04:29,710 --> 00:04:32,900 So from unique cats, three select everything. 76 00:04:32,950 --> 00:04:34,270 They all look the same. 77 00:04:34,270 --> 00:04:35,140 Boingo one. 78 00:04:35,140 --> 00:04:40,090 Boingo one but cat ID automatically incremented for each one of them. 79 00:04:40,090 --> 00:04:46,150 So this is really useful because now I have this primary key that doesn't require any effort on my part 80 00:04:46,150 --> 00:04:47,140 to be unique. 81 00:04:47,140 --> 00:04:50,080 I don't have to keep track of a number and add one or anything like that. 82 00:04:50,080 --> 00:04:55,360 I just insert a value and we automatically get that unique ID, so that's super common to do. 83 00:04:55,660 --> 00:05:01,130 We combine auto increment with primary key all the time and you can do it on the same line. 84 00:05:01,150 --> 00:05:02,620 Just want to be clear about that. 85 00:05:02,620 --> 00:05:12,070 If I copied this, let's say I'm doing it from a script like over here in a file, I can do this auto 86 00:05:12,070 --> 00:05:15,850 increment primary key. 87 00:05:15,880 --> 00:05:22,330 It doesn't have to be on separate lines and let's call this unique cat forward, which is annoying, 88 00:05:22,330 --> 00:05:25,270 but let's just call it that and run this script. 89 00:05:25,270 --> 00:05:30,400 I've got an extra comma here, so it's going to complain to me, make sure I'm in the pet shop database. 90 00:05:30,400 --> 00:05:31,150 All right. 91 00:05:31,150 --> 00:05:33,520 And how do I run u here? 92 00:05:33,520 --> 00:05:35,560 I need to click Lightning Bolt. 93 00:05:37,700 --> 00:05:45,310 Let's describe a unique Cat four and you'll see that we have that primary key. 94 00:05:45,320 --> 00:05:47,060 We have auto increment set up. 95 00:05:47,060 --> 00:05:51,830 And if I were to insert a cat insert into unique cat. 96 00:05:52,760 --> 00:05:53,600 For. 97 00:05:55,800 --> 00:05:56,910 Values. 98 00:05:56,970 --> 00:05:58,920 Let's say I'm inserting nothing. 99 00:05:59,040 --> 00:06:00,240 It's just going to be empty. 100 00:06:02,440 --> 00:06:03,970 I'll do it a couple of times. 101 00:06:05,820 --> 00:06:09,480 And then I select everything from my unique cats for. 102 00:06:11,080 --> 00:06:14,210 We see that ID did work the primary key. 103 00:06:14,230 --> 00:06:16,890 It automatically increments 1 to 3 and so on. 104 00:06:16,960 --> 00:06:22,180 And the last thing I should mention is when you set up the auto increment, if I did try and insert 105 00:06:22,180 --> 00:06:33,250 a cat that manually had a cat ID that was already in use, like insert in two unique cats for let's 106 00:06:33,250 --> 00:06:38,770 say I'm adding the cat ID myself, which you normally wouldn't do if you have auto increment set up. 107 00:06:38,770 --> 00:06:44,260 But if I wanted to add a cat that has the idea of three, I'm going to get an error because there's 108 00:06:44,260 --> 00:06:46,640 already a cat in there with cat ID of three. 109 00:06:46,660 --> 00:06:53,290 Just because we have auto increment doesn't mean that the regular sort of primary key constraint goes 110 00:06:53,290 --> 00:06:53,870 away. 111 00:06:53,890 --> 00:07:00,010 It still has to be unique, even if I'm not the one usually making this number right. 112 00:07:00,010 --> 00:07:02,560 It's I'm relying on the auto increment capability. 113 00:07:02,560 --> 00:07:07,270 If I try to insert a duplicate value, I'll get an error and it won't insert. 114 00:07:08,230 --> 00:07:11,020 So that's primary key and auto increment. 115 00:07:11,020 --> 00:07:12,940 You'll see them together all the time.