WEBVTT 1 00:00:00.290 --> 00:00:01.580 Hi guys, and welcome back. 2 00:00:01.580 --> 00:00:02.440 Hope you're doing great. 3 00:00:02.440 --> 00:00:05.390 In this video we're going to learn about relational data. 4 00:00:05.390 --> 00:00:06.540 In a bit more depth, 5 00:00:06.540 --> 00:00:09.200 we're going to learn about Primary and Foreign Keys. 6 00:00:09.200 --> 00:00:11.170 And although we've mentioned these a little bit in the past, 7 00:00:11.170 --> 00:00:14.230 but now it's gonna all be much clearer. 8 00:00:14.230 --> 00:00:16.420 So here we've got two tables, 9 00:00:16.420 --> 00:00:19.040 we have the User's table on the left 10 00:00:19.040 --> 00:00:21.780 and the Accounts table on the right. 11 00:00:21.780 --> 00:00:24.430 On the left, we have information about users. 12 00:00:24.430 --> 00:00:28.240 And often we assign each row a ID column, 13 00:00:28.240 --> 00:00:30.390 which is unique to each row. 14 00:00:30.390 --> 00:00:34.030 So the first user with ID one has a name of John Smith, 15 00:00:34.030 --> 00:00:37.270 ID two has a name of Rolf Smith and so on. 16 00:00:37.270 --> 00:00:41.860 On the right, we've got our holder ID column. 17 00:00:41.860 --> 00:00:43.350 As we learned previously, 18 00:00:43.350 --> 00:00:47.660 the holder ID maps to the ID column on the User's table. 19 00:00:47.660 --> 00:00:50.210 Each account also has an ID column, 20 00:00:50.210 --> 00:00:53.690 which uniquely identifies the account itself. 21 00:00:53.690 --> 00:00:58.150 So we can easily see that user with ID one 22 00:00:58.150 --> 00:01:01.760 is mapped to account with ID three. 23 00:01:01.760 --> 00:01:04.200 Normally these unique numbers, 24 00:01:04.200 --> 00:01:07.510 these ID properties that uniquely identify each row, 25 00:01:07.510 --> 00:01:11.010 and that we use to map rows from one table to another, 26 00:01:11.010 --> 00:01:13.450 we call those Primary Keys. 27 00:01:13.450 --> 00:01:14.800 And when you're creating a table, 28 00:01:14.800 --> 00:01:16.180 what you're gonna do is you're gonna use 29 00:01:16.180 --> 00:01:21.140 the Primary Key keywords besides the ID Integer column 30 00:01:21.140 --> 00:01:24.990 to the note that this is the primary key of the table. 31 00:01:24.990 --> 00:01:26.940 So here you can see on the left, for example, 32 00:01:26.940 --> 00:01:28.470 when we create a table of the users, 33 00:01:28.470 --> 00:01:31.270 we say that the first column is ID, 34 00:01:31.270 --> 00:01:33.010 the type is integer, 35 00:01:33.010 --> 00:01:35.150 and it is the Primary Key. 36 00:01:35.150 --> 00:01:37.170 The same thing for the table on the right, 37 00:01:37.170 --> 00:01:38.810 the Accounts table. 38 00:01:38.810 --> 00:01:41.950 The holder ID column on the right though, 39 00:01:41.950 --> 00:01:44.000 is not a Primary Key, 40 00:01:44.000 --> 00:01:47.340 because it's not used to identify these rows. 41 00:01:47.340 --> 00:01:51.970 Instead, it is used as a mapping to a different table. 42 00:01:51.970 --> 00:01:55.725 You can see that the holder ID values match exactly 43 00:01:55.725 --> 00:01:58.420 the Primary Key on the User's table. 44 00:01:58.420 --> 00:02:01.130 That is why it's called a Foreign Key. 45 00:02:01.130 --> 00:02:02.520 And that is how you write it. 46 00:02:02.520 --> 00:02:05.400 There on the bottom right we've got Foreign Key 47 00:02:05.400 --> 00:02:09.650 and then the column holder ID that we already defined above, 48 00:02:09.650 --> 00:02:14.310 references, the User's table, the ID column there. 49 00:02:14.310 --> 00:02:17.270 So this is a little bit of a complicated construct there. 50 00:02:17.270 --> 00:02:20.100 But notice that it does not define a new column, 51 00:02:20.100 --> 00:02:22.740 the column holder ID was defined above, 52 00:02:22.740 --> 00:02:24.560 it just says that this is a Foreign Key, 53 00:02:24.560 --> 00:02:28.200 and that the reference is the User's table, the ID column. 54 00:02:28.200 --> 00:02:31.620 The important part is that when we do this, 55 00:02:31.620 --> 00:02:34.810 most database systems including PostgresSQL, 56 00:02:34.810 --> 00:02:39.120 and SQLlite too if it's set up correctly, 57 00:02:39.120 --> 00:02:41.720 will automatically match these two. 58 00:02:41.720 --> 00:02:45.690 And will say that you can't have invalid data. 59 00:02:45.690 --> 00:02:48.900 So if we have an account with holder ID of five, 60 00:02:48.900 --> 00:02:51.330 but no user with ID of five, 61 00:02:51.330 --> 00:02:53.880 that is an inconsistency in our data. 62 00:02:53.880 --> 00:02:54.810 And like I said, 63 00:02:54.810 --> 00:02:57.950 most database systems are going to complain. 64 00:02:57.950 --> 00:03:00.200 There's two problems with this inconsistency. 65 00:03:00.200 --> 00:03:02.910 An accounted with no user is useless. 66 00:03:02.910 --> 00:03:05.120 So if we have an account with a holder ID of five, 67 00:03:05.120 --> 00:03:06.780 but there's no user to match that, 68 00:03:06.780 --> 00:03:08.970 then that account is essentially orphaned, 69 00:03:08.970 --> 00:03:10.700 and it's not that useful. 70 00:03:10.700 --> 00:03:12.310 But if you create a new user, 71 00:03:12.310 --> 00:03:14.610 and it so happens to have the ID of five, 72 00:03:14.610 --> 00:03:18.150 then that user is automatically gonna map to that account. 73 00:03:18.150 --> 00:03:19.440 And it's gonna inherit that account 74 00:03:19.440 --> 00:03:21.150 even if it wasn't theirs originally, 75 00:03:21.150 --> 00:03:23.620 so that's quite dangerous, as well. 76 00:03:23.620 --> 00:03:26.350 So those are the two main problems we have with this invalid 77 00:03:26.350 --> 00:03:28.100 or inconsistent data. 78 00:03:28.100 --> 00:03:30.860 That's why most database systems 79 00:03:30.860 --> 00:03:33.410 are going to set up these constraints 80 00:03:33.410 --> 00:03:35.510 that prevents you from doing that. 81 00:03:35.510 --> 00:03:37.790 So adding this Foreign Key relationship 82 00:03:37.790 --> 00:03:40.690 is going to enforce some constraints if you use Postgres, 83 00:03:40.690 --> 00:03:43.130 or SQLlite with Foreign Key Support. 84 00:03:43.130 --> 00:03:46.280 So you won't be able to have these inconsistencies. 85 00:03:46.280 --> 00:03:49.040 For example, let's say you have a user with ID five, 86 00:03:49.040 --> 00:03:51.069 and an account with holder ID five, 87 00:03:51.069 --> 00:03:53.170 Postgres, for example, won't allow you 88 00:03:53.170 --> 00:03:56.630 to delete the user unless you first delete the account. 89 00:03:56.630 --> 00:03:58.610 So that's the sort of prevention 90 00:03:58.610 --> 00:04:00.370 that they're gonna do for you. 91 00:04:00.370 --> 00:04:02.940 More information about all of this is in the e-book. 92 00:04:02.940 --> 00:04:05.220 We've got a few examples there and all that. 93 00:04:05.220 --> 00:04:06.590 So do check that out 94 00:04:06.590 --> 00:04:08.610 if you want to read more on the subject. 95 00:04:08.610 --> 00:04:11.710 That's all linked in the resources section of this lecture. 96 00:04:11.710 --> 00:04:13.050 Thank you guys for watching. 97 00:04:13.050 --> 00:04:14.600 I'll see you in the next video.