1 00:00:00,990 --> 00:00:06,180 So we need to set up some foreign keys in our database and we'll do the reservations table first because 2 00:00:06,180 --> 00:00:06,970 it just has one. 3 00:00:07,260 --> 00:00:13,210 It has a foreign key from this column remedy linking to the ID column in the room table. 4 00:00:13,560 --> 00:00:14,630 So how do we do that? 5 00:00:14,640 --> 00:00:17,750 And fortunately, SOTA makes this remarkably easy. 6 00:00:17,820 --> 00:00:25,380 So I'm going to go to my terminal and I'm going to write SOTA, generate FIDs, create Akay for foreign 7 00:00:25,380 --> 00:00:31,400 key for reservations table and it creates my migration's. 8 00:00:31,410 --> 00:00:36,260 So let's open one up, we'll open up the newest one, which is this one right here to make sure it's 9 00:00:36,270 --> 00:00:37,230 the up migration. 10 00:00:37,770 --> 00:00:41,850 And I'm just going to paste in the actual migration because it's a little tricky to type and I don't 11 00:00:41,850 --> 00:00:42,720 want to make mistakes. 12 00:00:43,140 --> 00:00:50,850 So the syntax is add, underscore for and underscore key that tells SOTA we're going to create a foreign 13 00:00:50,880 --> 00:00:55,380 foreign key, then an opening parentheses that in quotes the name of the table that's going to hold 14 00:00:55,380 --> 00:00:57,840 the foreign key, that's reservations in this case. 15 00:00:59,010 --> 00:01:04,590 Then the name of the column, that is the foreign key and that's called room ID in our reservations 16 00:01:04,590 --> 00:01:05,040 table. 17 00:01:05,310 --> 00:01:11,760 And that links to in these curly brackets right here on the room's table in square brackets with quotation 18 00:01:11,760 --> 00:01:15,230 marks around it, the name of the column that remedy should link to. 19 00:01:15,690 --> 00:01:22,680 So it says on the table reservations for the column room ID create a foreign key that points to the 20 00:01:22,680 --> 00:01:24,780 rooms table and the ID column. 21 00:01:25,140 --> 00:01:27,480 And then what do you want this foreign key to do? 22 00:01:27,930 --> 00:01:34,650 Well, to start with, when someone deletes the that corresponding entry in the room table, I'm just 23 00:01:34,650 --> 00:01:40,170 going to say Kaskade also delete this reservation when someone updates the foreign key. 24 00:01:40,410 --> 00:01:47,850 If it used to be ID one and somebody changes it to 82 on the table rooms, then again, update the foreign 25 00:01:47,850 --> 00:01:49,640 key here, just Kaskade those changes. 26 00:01:49,690 --> 00:01:52,230 So let's try broadening that migration and see what happens. 27 00:01:52,590 --> 00:01:56,430 So sota migrate and there goes. 28 00:01:56,580 --> 00:02:02,730 We now have a foreign key relationship and I can go over to my I'm going to use post to go again and 29 00:02:02,730 --> 00:02:08,900 look at this so I can look at the reservations table and there it is, there's the description of it. 30 00:02:09,060 --> 00:02:10,710 So what is this actually happened. 31 00:02:10,710 --> 00:02:14,910 So I can actually look at where is it here, reservations. 32 00:02:15,600 --> 00:02:17,160 I don't want to look at the structure. 33 00:02:17,160 --> 00:02:23,010 I want to look at the DDL, the data description, language, and it actually says here that I have 34 00:02:23,010 --> 00:02:25,380 my reservations and I have room ID. 35 00:02:25,590 --> 00:02:29,940 It references rooms, ID undelete Kaskade on update Kaskade. 36 00:02:30,210 --> 00:02:32,060 So my foreign key is now there. 37 00:02:32,340 --> 00:02:34,320 Now how do I get rid of a foreign key. 38 00:02:34,320 --> 00:02:36,060 I need to write a down migration. 39 00:02:38,530 --> 00:02:43,360 Well, the syntax for that's actually pretty simple, let's go back over to our terminal and open our 40 00:02:43,360 --> 00:02:47,830 down migration for the foreign chief reservations and just right. 41 00:02:47,830 --> 00:02:51,880 Drop foreign Haidian numbers. 42 00:02:51,880 --> 00:02:57,370 Better for our Aegean key at the table. 43 00:02:57,370 --> 00:02:58,730 Name is reservations. 44 00:02:58,750 --> 00:03:02,160 That's where the foreign he lives and the foreign key name. 45 00:03:02,200 --> 00:03:05,650 Now, that is not as simple as you might think. 46 00:03:06,670 --> 00:03:09,460 The foreign keys actually named by postgrads. 47 00:03:09,460 --> 00:03:12,670 And it uses a particular syntax for doing that. 48 00:03:12,670 --> 00:03:13,460 And it's pretty simple. 49 00:03:15,250 --> 00:03:17,200 So I want to go to my postgrads. 50 00:03:17,200 --> 00:03:18,610 I want to go to bookings. 51 00:03:19,000 --> 00:03:23,020 I want to look at my schema. 52 00:03:24,260 --> 00:03:24,980 Tables. 53 00:03:28,540 --> 00:03:37,300 Reservations, foreign chiefs, and there it is, it is called reservations, underscore rooms, underscore 54 00:03:37,300 --> 00:03:38,890 idy, underscore coffeecake. 55 00:03:38,920 --> 00:03:44,110 So let's go back in their reservations underscore and what was it? 56 00:03:45,040 --> 00:03:55,630 Underscore rooms, underscore idee, underscore EPK reservation rooms, IDFC of migrate down and there 57 00:03:55,630 --> 00:03:56,040 it is. 58 00:03:56,050 --> 00:04:01,690 So it deletes it and I can put it back in so to migrate and it works great. 59 00:04:02,140 --> 00:04:03,460 So did that flunkey. 60 00:04:03,460 --> 00:04:04,420 Let's add the rest of them. 61 00:04:04,420 --> 00:04:06,210 Where else do we need foreign keys. 62 00:04:06,550 --> 00:04:08,040 Well let's do that in the next lecture. 63 00:04:08,050 --> 00:04:09,340 I think this one's long enough.