WEBVTT 00:07.280 --> 00:14.180 So in the previous lecture we are able to create a new table and that is for phones. 00:14.210 --> 00:18.230 Therefore we have students table and have the phone table. 00:18.260 --> 00:22.670 Now we want to have a difference between students and phone. 00:22.700 --> 00:28.760 We want each student to have a phone, and we want to be able to reference both of this table. 00:28.760 --> 00:32.030 And that is when foreign key comes into play. 00:32.060 --> 00:38.240 So when we talk about a foreign key, a foreign key is a column or a group of columns in a table that 00:38.240 --> 00:41.750 uniquely identifies a row in another table. 00:41.750 --> 00:43.430 So there must be up to two tables. 00:43.430 --> 00:49.190 And that is why I was able to create this table called phone table in the previous lecture. 00:49.190 --> 00:56.270 So a foreign key establishes a link between the data and the two tables by referencing the primary key 00:56.300 --> 00:59.540 or a unique constraint of the reference table. 00:59.540 --> 01:07.350 So that table that contains a foreign key is referred to as the reference interval or a child table. 01:07.500 --> 01:08.220 All right. 01:08.220 --> 01:11.850 So I have this table and I have an active constraint. 01:11.850 --> 01:14.220 And we all know about these two tables. 01:14.250 --> 01:19.350 Now let me go ahead and open up my Visual Studio. 01:19.350 --> 01:21.810 So I have the student table which I have here. 01:21.810 --> 01:24.270 And I have the front table which is right in here. 01:24.300 --> 01:32.250 Now I have about 1000 data to deal with in both of these, and for quality purposes, we want to just 01:32.250 --> 01:39.390 limit the number will be just like three from the front table and maybe like 4 or 3 from the student 01:39.390 --> 01:41.760 table, so that we can be able to see the results. 01:41.760 --> 01:47.250 Because if you have a large data, remember it is really, really so bogus for us to see what we are 01:47.250 --> 01:47.700 doing. 01:47.730 --> 01:50.010 Therefore, I open up my atom. 01:50.040 --> 01:52.920 I have an atom editor, which I have right in the end. 01:52.920 --> 02:02.880 And here I'm going to insert this three, uh, rows from the phone and these four from the students 02:02.880 --> 02:03.420 table. 02:03.420 --> 02:06.630 And then I'm going to create a new table called phone. 02:06.630 --> 02:08.930 And I'm going to create a new table called students. 02:08.930 --> 02:12.800 And we're going to insert everything right back inside here. 02:12.920 --> 02:17.630 Now, what I actually did is that I'm going to minimize this. 02:17.660 --> 02:26.090 Go over to your students table and you can open another tab called uh, go ahead and open up that file. 02:26.090 --> 02:31.520 And you can call that file maybe student phone dot SQL. 02:31.550 --> 02:32.180 Okay. 02:32.180 --> 02:35.210 But I just created that with my Acton. 02:35.210 --> 02:43.100 And that is why I have this in atom called students I think phone dot SQL because I just want that to 02:43.130 --> 02:44.330 be unique. 02:44.330 --> 02:47.240 So you don't need to go ahead and open in another editor. 02:47.270 --> 02:48.860 Go ahead and use just send editor. 02:48.860 --> 02:53.990 And what you need to do is go over to phone and then you copy the create table. 02:53.990 --> 02:58.970 We're going to just use that and then go over to students and copy exactly this. 02:58.970 --> 03:04.310 Then you can copy it maybe the very first three or the very first four on this end right in here. 03:04.340 --> 03:04.880 Okay. 03:04.910 --> 03:06.020 That is what I just did. 03:06.020 --> 03:06.530 Exactly. 03:06.530 --> 03:08.660 So there is no thing back here. 03:08.690 --> 03:09.290 Okay. 03:09.410 --> 03:15.550 I copied the phone because I want the phone to come first, and then I want the table for students to 03:15.580 --> 03:16.300 come second. 03:16.330 --> 03:18.520 Then this doesn't matter. 03:18.520 --> 03:19.900 Whichever comes first. 03:19.930 --> 03:20.530 All right. 03:20.530 --> 03:22.630 It doesn't matter whichever that comes first. 03:22.660 --> 03:24.850 In the strength comes first or the phone comes first. 03:24.880 --> 03:30.010 But now we are going to have something in common. 03:30.010 --> 03:35.170 Because if you look at this table is different and this table is actually different. 03:35.170 --> 03:37.960 That is not the referencing these two tables. 03:37.960 --> 03:42.160 And that is where we are going to concentrate on. 03:42.190 --> 03:44.410 We're going to have some reference in both tables. 03:44.440 --> 03:46.060 And let's go ahead and do that. 03:46.390 --> 03:52.570 So we have to first of all move over to the students table because we want the student to reference 03:52.570 --> 03:58.240 the phone, meaning that we want each of the student to have a phone attached to them. 03:58.240 --> 04:00.490 So we want to concentrate on the strings. 04:00.490 --> 04:02.830 I use that to reference the phone table. 04:02.830 --> 04:04.090 So go over to student. 04:04.090 --> 04:06.790 I'm going to put a comma right in here. 04:06.790 --> 04:10.150 And uh let's go ahead and uh move down. 04:10.150 --> 04:13.390 And then let's go ahead and make this country to be not null. 04:13.420 --> 04:13.990 All right. 04:14.020 --> 04:16.300 Not null. 04:16.330 --> 04:23.020 And then we go right here and I'm going to use the id which is the ID right in here. 04:23.020 --> 04:25.840 And I'm going to give that a name maybe the phone ID. 04:25.870 --> 04:28.870 So I'm going to say this is a phone underscore ID. 04:29.110 --> 04:36.070 And we're going to use no longer the big area because this autoincrement we want to use the big INT 04:36.070 --> 04:39.970 because that will help us to hold a right data and risk. 04:39.970 --> 04:46.120 When I say big int and it's going to refresh the font table. 04:46.150 --> 04:53.200 Therefore I'm going to say uh refresh and uh, and refreshes the phone. 04:53.950 --> 04:54.640 Okay. 04:54.640 --> 05:01.120 And then we're going to pass in this phone ID which is this ID right in here. 05:01.120 --> 05:09.460 And for us to do that we just go ahead and put in ID and then we put a comma right in here because we 05:09.460 --> 05:16.120 need a unique constraint for this particular, uh, column right in here. 05:16.150 --> 05:18.070 So we're going to make that to be unique. 05:19.630 --> 05:22.480 So currency unique. 05:22.480 --> 05:29.500 And then we'll go ahead and pass in the phone underscore ID. 05:29.590 --> 05:35.590 Now if you have done this what you need to do is to go ahead and import this. 05:35.620 --> 05:36.280 Okay. 05:36.310 --> 05:37.900 Go ahead and save this. 05:37.900 --> 05:42.220 Try to save that because I didn't import that just as we always do. 05:42.250 --> 05:50.620 Now if you want to import that the squad head and move over to SQL shell because we have table course 05:50.740 --> 05:52.240 there's a table called phone. 05:52.240 --> 05:55.960 If we try to import that we are going to run into an error. 05:55.960 --> 05:59.830 So let's go ahead and see what we see and check our database table. 05:59.860 --> 06:04.240 You can see we have a phone table and help students develop. 06:04.270 --> 06:08.830 All we need to do is that we go ahead and drop these two tables. 06:08.860 --> 06:15.460 So after we drop this two table that then we can now go ahead and uh do what. 06:15.490 --> 06:17.350 Import the new table. 06:17.350 --> 06:23.770 And to drop this table, all I need to do is to go ahead and say drop table, and the table I want to 06:23.770 --> 06:31.000 drop is a student, and then go ahead and put a semicolon at the end and hit enter and it will drop. 06:31.000 --> 06:34.540 And I'm going to drop the second table drop table. 06:34.540 --> 06:37.090 And then the table is fun. 06:37.090 --> 06:42.910 And go ahead and put a semicolon at the end and hit enter and have dropped the second table. 06:42.910 --> 06:49.330 So if I go ahead and say blackboard slash d t for the database table and hit enter, I can see did not 06:49.330 --> 06:51.070 find any relation. 06:51.070 --> 06:59.530 And now I can go ahead and import the new table which is a student underscore form dot SQL. 06:59.530 --> 07:09.100 And for us to import these I have this table right inside uh, this folder uh the the name of that folder 07:09.100 --> 07:17.020 is uh program data to go ahead and open up the folder name so it can be good to go right in there. 07:17.020 --> 07:22.860 So that is right inside the downloads and uh programs or initiatives. 07:22.860 --> 07:25.200 Go ahead and copy the parts as usual. 07:25.200 --> 07:33.180 And if I copy the part, I'm going to open up, uh, this, uh, notepad and I'll paste that. 07:33.180 --> 07:37.500 And I've already done that immediately and change all these to a forward slash. 07:37.500 --> 07:38.670 Ensure that you do that. 07:38.670 --> 07:40.530 Then go ahead and copy that. 07:40.530 --> 07:43.560 And uh go ahead and minimize that. 07:43.590 --> 07:46.890 Always go ahead and then go ahead and click this. 07:46.890 --> 07:49.800 First of all before we go ahead and import it. 07:49.800 --> 07:54.810 So go ahead and see backward slash I and then press the button you just copied. 07:54.810 --> 07:57.450 And now I'll go ahead and hit enter. 07:57.450 --> 08:00.480 And you can see I have the table created for me. 08:00.600 --> 08:07.320 And this table has a the four rows for the students and three rows for falls. 08:07.320 --> 08:14.880 And if I go ahead and say backward slash and hit enter, I still have my phone and students table right 08:14.880 --> 08:15.390 in here. 08:15.390 --> 08:16.440 I hope that is cool. 08:16.440 --> 08:25.110 And, uh, we can also go ahead and, uh, check each of them one by one and see them as and the properties 08:25.110 --> 08:26.580 of each of these tables. 08:27.330 --> 08:31.020 So I'll just go ahead and say the selector. 08:32.550 --> 08:40.980 Select all from students and put a semicolon at the end and hit enter. 08:40.980 --> 08:43.590 And I have these right in here. 08:43.620 --> 08:48.300 Now if I do that for the second one and go ahead and say select. 08:50.310 --> 08:56.700 All from foreign and put the stomach rod end and hit enter. 08:56.700 --> 09:00.480 And you can see I have the second table for the form right in here. 09:00.480 --> 09:06.870 And now in the next video we go ahead and uh, work with this and see how the foreign key actually works, 09:06.870 --> 09:10.560 because you can see that under the form for students. 09:10.800 --> 09:12.240 I can see the font ID here. 09:12.240 --> 09:14.160 We have nothing right in here. 09:14.160 --> 09:17.670 And in the next video we go ahead and work with that. 09:17.670 --> 09:19.170 So thank you so much. 09:19.170 --> 09:22.500 And I'm going to see you in the next video lecture.