WEBVTT 1 00:00:00.240 --> 00:00:01.950 Hi guys and welcome back. 2 00:00:01.950 --> 00:00:03.220 In this video, we're going to talk 3 00:00:03.220 --> 00:00:07.170 about composite primary keys or how to make two columns 4 00:00:07.170 --> 00:00:09.490 the primary key, as opposed to one column 5 00:00:09.490 --> 00:00:12.240 or more than two columns, if you want. 6 00:00:12.240 --> 00:00:14.470 We know that primary keys are primarily used 7 00:00:14.470 --> 00:00:17.640 for linking tables with foreign keys. 8 00:00:17.640 --> 00:00:19.440 But what we haven't explicitly said 9 00:00:19.440 --> 00:00:21.850 is that when you set a primary key, 10 00:00:21.850 --> 00:00:24.750 it also adds an index to the column. 11 00:00:24.750 --> 00:00:26.800 We've learned what indexes are, 12 00:00:26.800 --> 00:00:29.730 so when you create a primary key, that also creates an index 13 00:00:29.730 --> 00:00:30.630 for that column. 14 00:00:30.630 --> 00:00:33.450 And primary keys have to be unique 15 00:00:33.450 --> 00:00:35.940 so creating a primary key also adds 16 00:00:35.940 --> 00:00:38.740 a unique constraint to the column. 17 00:00:38.740 --> 00:00:41.030 Composite primary keys work in the same way, 18 00:00:41.030 --> 00:00:43.680 but instead of the single column being treated 19 00:00:43.680 --> 00:00:45.040 for all of those things, 20 00:00:45.040 --> 00:00:47.560 it is the combination of two columns. 21 00:00:47.560 --> 00:00:50.180 So a composite primary key can have two or more columns 22 00:00:50.180 --> 00:00:53.280 and the columns get a composite index 23 00:00:53.280 --> 00:00:56.070 and they also get a composite unique constraint 24 00:00:56.070 --> 00:00:59.200 where the individual columns no longer have to be unique, 25 00:00:59.200 --> 00:01:01.720 but the combination of columns have to be unique. 26 00:01:01.720 --> 00:01:03.150 Let's take a look at an example. 27 00:01:03.150 --> 00:01:06.380 Here we've got a table where we have the account number, 28 00:01:06.380 --> 00:01:08.580 the sort code and the username. 29 00:01:08.580 --> 00:01:11.120 If you're not familiar with UK banking accounts, 30 00:01:11.120 --> 00:01:13.410 then this is how you came banking accounts work. 31 00:01:13.410 --> 00:01:15.490 You have the account number and the sort code 32 00:01:15.490 --> 00:01:18.260 which is the branch number equivalent. 33 00:01:18.260 --> 00:01:20.300 Then in this table, we have the primary key 34 00:01:20.300 --> 00:01:23.910 as the combination of account number and sort code. 35 00:01:23.910 --> 00:01:26.320 Let's take a look at some sample data. 36 00:01:26.320 --> 00:01:28.750 Here you can see we've got five users 37 00:01:28.750 --> 00:01:31.510 and three of them have the same account number, 38 00:01:31.510 --> 00:01:34.130 but they have a different sort code. 39 00:01:34.130 --> 00:01:36.080 This would be perfectly valid, 40 00:01:36.080 --> 00:01:38.670 even though normally if we have the account number only 41 00:01:38.670 --> 00:01:40.910 as a primary key, this would be invalid 42 00:01:40.910 --> 00:01:43.558 because the primary key is the combination of columns, 43 00:01:43.558 --> 00:01:45.340 none of them are duplicates. 44 00:01:45.340 --> 00:01:47.890 They are all different in combination. 45 00:01:47.890 --> 00:01:49.280 So if we do the same thing 46 00:01:49.280 --> 00:01:52.760 but now we do have duplicate combination of columns, 47 00:01:52.760 --> 00:01:56.230 this is no longer okay, this violates the unique constraint 48 00:01:56.230 --> 00:01:58.450 for the composite primary key. 49 00:01:58.450 --> 00:01:59.610 At this point, I'll also say that 50 00:01:59.610 --> 00:02:02.240 you can apply unique constraints to any column, 51 00:02:02.240 --> 00:02:03.740 not just primary keys. 52 00:02:03.740 --> 00:02:06.370 And when you do that, you can apply them to single columns 53 00:02:06.370 --> 00:02:08.500 or to composite columns as well. 54 00:02:08.500 --> 00:02:10.830 When we're using composite foreign keys, it's very simple. 55 00:02:10.830 --> 00:02:12.650 Just refer to both columns instead of one 56 00:02:12.650 --> 00:02:16.170 using the usual comma separated column nomenclature, 57 00:02:16.170 --> 00:02:17.360 such as we see here. 58 00:02:17.360 --> 00:02:18.730 At the bottom, we've got foreign key 59 00:02:18.730 --> 00:02:20.760 and then inside of brackets, instead of the single account, 60 00:02:20.760 --> 00:02:22.950 we put two accounts with a comma in the middle 61 00:02:22.950 --> 00:02:25.490 and then references the accounts table 62 00:02:25.490 --> 00:02:28.640 and again, comma separated column names. 63 00:02:28.640 --> 00:02:31.070 We can simplify foreign key constraints, by the way. 64 00:02:31.070 --> 00:02:34.350 If the parent and the child table use the same name columns, 65 00:02:34.350 --> 00:02:36.300 we can simplify it to something like this. 66 00:02:36.300 --> 00:02:37.840 Foreign key of account number 67 00:02:37.840 --> 00:02:40.060 and sort code references the accounts table 68 00:02:40.060 --> 00:02:42.740 and Postgres is smart enough to understand 69 00:02:42.740 --> 00:02:46.430 that you refer to the account number and sort code columns 70 00:02:46.430 --> 00:02:48.310 in the accounts table. 71 00:02:48.310 --> 00:02:50.110 All right, that's everything for this video. 72 00:02:50.110 --> 00:02:50.943 Just wanted to tell you 73 00:02:50.943 --> 00:02:55.130 that you can use composite primary keys and what they mean. 74 00:02:55.130 --> 00:02:58.030 Thank you guys for watching, I'll see you in the next one.