WEBVTT 00:07.610 --> 00:10.370 I welcome you back again to another video lecture in MySQL. 00:10.370 --> 00:15.050 And in this video lecture we go ahead and look at foreign keys. 00:15.050 --> 00:18.710 So in MySQL we have what is called foreign keys. 00:18.710 --> 00:22.160 And this helps us to be able to link to tables. 00:22.160 --> 00:26.930 So if we want to communicate between different tables you go ahead and use the foreign keys. 00:26.930 --> 00:32.960 Because this foreign key helps you to be able to interact between two tables. 00:32.990 --> 00:36.170 Let's go ahead and say we have a table called Teachers Table. 00:36.170 --> 00:39.620 And we have another table called the subjects table. 00:39.620 --> 00:46.850 And a subject table is going to be linked to the teachers table so that our teacher communicated with 00:47.030 --> 00:49.130 different courses or different subjects. 00:49.130 --> 00:50.000 Adaptation. 00:50.210 --> 00:57.830 Let me say I have a teacher called uh Steve and the teachers table, and we have a subject called chemistry 00:57.830 --> 01:01.470 and the Subjects table. 01:01.920 --> 01:08.820 So on the teacher's table, our judge is going to be communicating with chemistry in the subjects table. 01:08.820 --> 01:12.810 So the teachers table and the subjects table will always interact. 01:12.810 --> 01:15.360 So each teacher in the teacher's table. 01:15.390 --> 01:20.040 Always have a subject assigned to them in the subjects table. 01:20.040 --> 01:31.020 So Judge Steve MLA and global we have different subjects like chemistry, biology, physics or history 01:31.050 --> 01:32.580 respectively assigned to them. 01:32.580 --> 01:34.380 And these are two different tables. 01:34.380 --> 01:36.540 So how are we to do this. 01:36.540 --> 01:39.870 So first of all let's go ahead and create a table called the Teachers Table. 01:39.870 --> 01:42.930 And then we'll go ahead and create a table called the subjects table. 01:42.930 --> 01:48.000 And we'll see how we can be able to create a foreign key that's going to interact between these two. 01:48.030 --> 01:51.270 Let's go ahead and create a table called dialogue. 01:51.270 --> 01:52.440 Create a table. 01:52.440 --> 01:55.650 And the name of the table will be teachers. 01:55.650 --> 02:00.430 And this is the first table we are creating warhead and put a semicolon at the end of this. 02:00.460 --> 02:05.800 Then we go ahead and, uh, create the teachers ID. 02:08.170 --> 02:14.020 Teachers underscore ID and, uh, we just need the teachers ID and their first name. 02:14.020 --> 02:16.300 So go ahead and add your last name. 02:16.300 --> 02:19.330 Anything you want to add under this table there's no problem. 02:19.330 --> 02:25.750 So the name is going to the teacher ID is going to be an integer data type. 02:25.750 --> 02:29.830 And then we're going to give you the primary key. 02:31.150 --> 02:35.260 And let's also make this to be autoincrement. 02:35.380 --> 02:42.070 So the teacher ID is integer data type primary key and auto increments. 02:42.070 --> 02:45.670 So the next one we need now is the first name. 02:45.700 --> 02:47.380 Just the first name only. 02:47.380 --> 02:50.560 And it's going to be varchar. 02:50.590 --> 02:52.480 That is what the data type is going to be. 02:53.500 --> 02:58.730 And we go ahead and give it to be dosed and put that is all. 02:58.730 --> 03:00.650 So you can learn how to select these. 03:00.650 --> 03:11.150 If you want to populate your table and say that, go ahead and say select all from teachers and press 03:11.150 --> 03:12.260 semicolon at the end. 03:12.260 --> 03:14.120 And let's go ahead and query this table. 03:14.300 --> 03:17.300 And right now you can go ahead and refresh your database. 03:17.300 --> 03:19.520 And you can see we have a test table. 03:19.520 --> 03:22.370 And if you go to foreign keys no nothing is there. 03:22.370 --> 03:26.630 If you go to index you can see we have one primary key there. 03:26.630 --> 03:33.230 And under the column we have our teachers ID and our first name. 03:34.310 --> 03:34.580 All right. 03:34.580 --> 03:36.830 So that is for teachers ID. 03:36.860 --> 03:42.560 Now let's go ahead and uh insert some rows into this. 03:42.560 --> 03:47.030 So under the first name and uh we go ahead and insert the teachers names. 03:47.030 --> 03:48.440 Let's go ahead and do that. 03:48.440 --> 03:55.150 So all I need to do right now is uh let's go ahead and insert Into. 03:58.090 --> 03:59.170 Into chess. 03:59.170 --> 04:04.810 And there we go ahead and put only the first name. 04:04.810 --> 04:10.900 The reason is because we have made the id to be also increment, and therefore we don't need to specify 04:10.900 --> 04:11.080 that. 04:11.080 --> 04:18.280 Again go ahead and say values and I'll go ahead and add that. 04:18.280 --> 04:21.580 So I just want to add only four of these. 04:27.790 --> 04:29.890 The comma then. 04:32.710 --> 04:35.740 A comma and then press semicolon. 04:35.740 --> 04:36.970 At the end of this only value. 04:36.970 --> 04:42.190 First name I'm going to add a is going to be judge. 04:43.000 --> 04:45.820 So the first name is going to be judge. 04:46.480 --> 04:48.130 Then the second name. 04:50.710 --> 05:03.530 Is Steve Then the next round is a normal one and the next one is global. 05:03.560 --> 05:03.980 All right. 05:04.010 --> 05:05.270 So this is where I want to add. 05:05.270 --> 05:08.060 And I'm going to use the select to populate that. 05:08.060 --> 05:11.120 You can be able to see that this guy had run this and check it out. 05:11.120 --> 05:17.720 So basically it's a query and we see the first name and the teachers ID we are automatically incremented 05:17.720 --> 05:18.470 for us. 05:18.800 --> 05:21.530 So now we have never to create the teachers table. 05:21.530 --> 05:22.310 What we need. 05:22.340 --> 05:25.970 Now we're going to link the teachers table to the subjects table. 05:25.970 --> 05:28.640 So we're going to start by creating the subjects table. 05:28.670 --> 05:32.810 And this is where we're going to add a foreign key to link to the teachers table we just created. 05:32.840 --> 05:36.410 Now we'll go ahead and remove everything we have right in here. 05:36.410 --> 05:38.630 And then we'll go ahead and create a new table. 05:38.630 --> 05:41.180 And I'm going to say create a table. 05:41.180 --> 05:44.720 And the table name is going to be subjects. 05:44.720 --> 05:49.190 And then we go ahead and press semicolon at the end. 05:49.220 --> 05:55.350 Now we go ahead and put in some columns. 05:56.070 --> 06:02.070 Subjects underscore ID that is your first one because you need to have an ID and that's going to be 06:02.100 --> 06:04.980 int the data type. 06:04.980 --> 06:07.770 Then let's also make this to be the primary key. 06:10.650 --> 06:11.490 Primary key. 06:11.490 --> 06:17.070 And also auto increment the camera. 06:17.280 --> 06:23.340 And then we're going to put in the subject name because these subjects are going to have a name. 06:24.900 --> 06:29.460 Subjects underscore name. 06:29.640 --> 06:30.150 All right. 06:30.180 --> 06:32.880 So this is going to be just varchar. 06:34.710 --> 06:39.300 And then we go ahead and make that to be just 15. 06:40.170 --> 06:46.980 Then after this we now need to create a column that's going to hold the teachers ID. 06:47.640 --> 06:55.120 We're going to create a column that's is going to hold the teachers ID and I'm going to go back to be 06:56.230 --> 06:57.100 any name. 06:57.100 --> 07:00.700 So you can call out to me any name. 07:00.700 --> 07:05.590 Let's go ahead and say that this is a teachers ID. 07:05.590 --> 07:06.820 So go ahead and say. 07:09.580 --> 07:14.590 Teachers underscore ID and then it's going to be just int. 07:16.180 --> 07:16.660 All right. 07:16.690 --> 07:18.700 Now put a comma. 07:18.880 --> 07:24.610 Then we are going to point that to the teachers table using the foreign key constraint. 07:24.610 --> 07:26.050 So go ahead and see. 07:30.250 --> 07:31.420 Foreign key. 07:32.740 --> 07:34.990 And then we are going to link. 07:35.020 --> 07:40.600 The foreign key is going to be link to the column we have right here called the teachers. 07:40.600 --> 07:41.770 Uh ID. 07:41.890 --> 07:44.470 And we're going to also reference that. 07:46.510 --> 07:56.030 It's It refreshes toward a table known as a sequence, and the table is also now known as a teacher's 07:56.060 --> 07:56.450 ID. 07:56.930 --> 07:57.830 All right. 07:57.920 --> 07:59.870 So now we have this. 07:59.870 --> 08:03.590 Let's go ahead and select all. 08:07.580 --> 08:12.980 Select all from subjects after a semicolon at the end. 08:13.010 --> 08:14.330 So I'm able to see our table. 08:14.330 --> 08:15.590 And this is the cutest query. 08:16.280 --> 08:19.160 And now we have this table called subjects. 08:19.160 --> 08:22.760 And it has subjects ID subject name and teachers ID. 08:22.880 --> 08:24.770 Let's go ahead and refresh this and check it out. 08:24.800 --> 08:27.470 Now I can still have a table called subjects. 08:27.500 --> 08:29.060 Go ahead and open up this table. 08:29.090 --> 08:34.670 Then under the column we have subjects subject name and teachers ID. 08:34.730 --> 08:37.220 Then under the index check this out. 08:37.250 --> 08:40.580 We have the one called primary key. 08:40.940 --> 08:43.850 And we have teachers ID. 08:43.970 --> 08:48.330 Remember that we did not specify any ID as a primary key right in here. 08:48.930 --> 08:50.010 Do you know what happens? 08:50.010 --> 08:56.970 It actually refers this to the teacher ID we created a teacher ID is also a primary ID is a primary 08:56.970 --> 08:57.330 key. 08:57.360 --> 08:58.620 That was a constraint. 08:58.650 --> 09:02.190 A primary key constraint was also added to that id. 09:02.460 --> 09:05.760 So the teacher id was created in the lab table on that. 09:05.760 --> 09:09.390 Teachers has a primary key constraint to eat. 09:09.390 --> 09:16.200 And another important thing which is not available under this teacher table which we have here is under 09:16.200 --> 09:20.250 the following tables have no foreign keys, but under the subjects go under the foreign keys. 09:20.370 --> 09:26.790 We have subjects underscore id f key underscore one. 09:27.120 --> 09:30.600 So you can also see now we have a foreign key now. 09:31.050 --> 09:33.720 And that is very very very interesting. 09:33.720 --> 09:37.320 So if you want to drop your foreign key is very simple. 09:37.350 --> 09:41.070 You can go ahead and see drop. 09:41.160 --> 09:46.630 And um Okay, this was a full table before we drove. 09:46.630 --> 09:49.960 So you can see the table. 09:49.960 --> 09:53.710 And the 212 is our subjects. 09:53.710 --> 09:58.570 And then we go ahead and uh drop. 10:03.190 --> 10:04.330 A foreign key. 10:04.330 --> 10:10.210 And then we're going to give the name of the foreign key which will have a subjects point and say this 10:10.210 --> 10:10.870 is a. 10:13.060 --> 10:17.230 Subjects underscore IB. 10:19.630 --> 10:22.120 F key then underscore one. 10:22.390 --> 10:24.490 Then put a semicolon at the end. 10:24.490 --> 10:32.920 And if we go ahead and run this check it out that as we run very well on a fresh disk and go under subjects 10:32.920 --> 10:34.750 and check the foreign keys. 10:34.750 --> 10:35.980 So guide and quizzes. 10:35.980 --> 10:39.280 So under the foreign keys we have no foreign key here. 10:39.310 --> 10:42.010 So that is gone. 10:42.040 --> 10:43.290 Our foreign key is gone. 10:43.680 --> 10:51.000 I want Eve, we go out on the water again and add the foreign key back and given it a unique name. 10:51.000 --> 10:52.080 Maybe give it our own name. 10:52.080 --> 10:53.520 Maybe not the name that is day. 10:53.550 --> 10:55.020 The one without the night. 10:55.050 --> 10:58.320 We create a new foreign key and give it a new name. 10:58.320 --> 11:02.700 So to do that you go ahead and say add constraint. 11:03.960 --> 11:05.430 So add a constraint. 11:05.460 --> 11:09.630 We give the name of the constraint as Guyana say it is a fk underscore. 11:11.730 --> 11:15.840 Subject underscore okay. 11:15.840 --> 11:19.440 Sorry say underscore because it's the ID that we need. 11:19.440 --> 11:22.890 So underscore ID alright. 11:22.920 --> 11:29.280 Now we can go ahead and see foreign key then. 11:34.020 --> 11:42.370 So foreign key we go ahead and put in the US ID that is what is needed. 11:42.370 --> 11:47.830 So what I need to do here is to say, okay, this is going to be in a lower case. 11:56.500 --> 12:01.270 Teachers underscore ID because this is teachers underscore ID. 12:01.450 --> 12:07.630 And that will go ahead and um a references that to the teachers table. 12:09.880 --> 12:18.880 And uh the column is still teachers underscore ID and that is all. 12:18.910 --> 12:21.220 Go ahead and place semicolon at the end of this. 12:21.220 --> 12:26.590 And let's go ahead and execute this right now. 12:26.590 --> 12:28.630 Let's go ahead and refresh this again and check it out. 12:28.630 --> 12:34.060 So one of the foreign keys you can see we have uh FCPS teacher underscore ID. 12:34.450 --> 12:36.040 And that is what we just created. 12:36.040 --> 12:38.590 So if you have an existing table you want to impute it. 12:38.590 --> 12:41.000 Just add is how it can be able to do that. 12:41.000 --> 12:47.030 So the excess of this is for us to actually know how to create this from the beginning, and how to 12:47.060 --> 12:49.220 add that to an existing table. 12:49.250 --> 12:51.080 Go ahead and remove everything right here. 12:51.140 --> 12:59.660 Now what I had on the deletes all the data we have on the subject so I can be able to create our own. 12:59.690 --> 13:08.090 So let me go ahead and say delete a from subjects and put a semicolon at the end. 13:08.090 --> 13:10.700 And let's go ahead and drop whatever we have right in there. 13:10.700 --> 13:12.350 And this is what we have. 13:12.350 --> 13:14.990 So I just want to explain that before we can continue. 13:14.990 --> 13:23.750 But before we continue, I want to set the subject ID to start from 101, because our teachers table 13:23.750 --> 13:31.190 starts auto increments from one, and I want to increment or auto increment the subject ID from to start 13:31.190 --> 13:32.090 from 101. 13:32.090 --> 13:38.160 That is, I want to have a chemistry 101, physics 102 and so on. 13:38.190 --> 13:39.300 I hope that is cool. 13:39.450 --> 13:44.070 Oh, I Rabbi, sorry, can alter our autoincrement from anywhere you want. 13:44.100 --> 13:48.690 So let's go ahead and say, um, alter table. 13:48.840 --> 13:56.700 And in all the table is going to be subjects and then we'll go ahead and, uh autoincrement. 13:56.700 --> 14:04.500 And uh, let us start from 101 because I want that to have a class code 101, 102, 103 and go ahead 14:04.500 --> 14:05.850 and do this. 14:05.880 --> 14:13.680 Now we have a query, and then we can now go ahead and insert some rows into that. 14:13.710 --> 14:15.450 Now let's go ahead and remove this. 14:15.450 --> 14:20.580 And I want to go ahead and insert some rules. 14:20.610 --> 14:28.590 Go ahead and say insert into subject. 14:29.070 --> 14:34.140 Then I just going to insert this subject name and the teachers ID. 14:34.650 --> 14:35.250 All right. 14:35.290 --> 14:40.450 Now go ahead and, uh, say I want to just input. 14:43.090 --> 14:44.620 Subjects. 14:45.010 --> 14:46.180 Subject name. 14:46.180 --> 14:50.290 Then the next one is going to be the teachers ID. 14:50.410 --> 14:56.620 Now, the reason why I'm inputting teachers ID is because I don't want the teachers ID to auto increment 14:56.650 --> 15:05.110 in the subjects table, because I can assign any subject to any teacher by just inputting the teacher 15:05.110 --> 15:06.190 ID, okay. 15:06.220 --> 15:14.560 And I want whenever I put a one on one, it'll automatically go to teacher with the ID one. 15:15.100 --> 15:15.730 You understand. 15:15.730 --> 15:17.830 So that is why I'm doing this. 15:17.860 --> 15:21.490 I must auto incrementing the teachers ID under the subject. 15:21.520 --> 15:23.110 I hope that is understandable. 15:23.140 --> 15:26.080 Now let's go ahead and our input values. 15:26.080 --> 15:29.860 And let's go ahead and input comma. 15:30.790 --> 15:32.230 So we have our teachers. 15:32.230 --> 15:35.150 So I'm going to add a for subjects. 15:36.470 --> 15:37.280 The comma. 15:39.650 --> 15:40.280 A comma. 15:42.710 --> 15:44.600 And put a semicolon at the end. 15:44.630 --> 15:49.580 Now let me say there for subject ID the one I have here is going to be. 15:53.480 --> 15:55.430 Physics then. 15:55.700 --> 16:03.110 Then I'm going to say physics is going to be assigned to teacher with the ID two. 16:03.500 --> 16:04.280 All right. 16:04.310 --> 16:07.100 Now the next one is going to be. 16:09.830 --> 16:10.880 Chemistry. 16:10.910 --> 16:13.790 And I want to assign that to teacher with ID one. 16:13.820 --> 16:15.290 You see I just want to alter that. 16:15.500 --> 16:21.620 Just making it to be teacher one first or physics to go to teacher one. 16:21.650 --> 16:22.850 Chemistry to teacher two. 16:22.880 --> 16:26.750 So I just want to manually add the teacher's ID on that subject. 16:26.900 --> 16:31.310 Now we go ahead and say our biology. 16:34.990 --> 16:42.970 And put a let me say it goes to the chat three and, uh. 16:45.040 --> 16:45.850 History. 16:46.540 --> 16:49.300 The message history goes to chat two as well. 16:49.300 --> 16:52.390 So you can see I signed a two. 16:52.420 --> 16:53.590 Subjects to one teacher. 16:53.800 --> 16:54.550 You see that. 16:54.550 --> 17:00.430 So here is possible because the teacher's ID on right here is not Autoincrement analyst. 17:00.430 --> 17:02.770 Go ahead and execute this and check it out. 17:02.890 --> 17:12.130 And you can see I have a physics Autoincrement 101 physics 101 and assigned to teachers ID two and physics 17:12.160 --> 17:20.740 one, chemistry 102 assigned to teacher ID one and uh biology one two, three assigned to teacher ID 17:21.880 --> 17:27.940 three and history is a 104 is assigned to teacher ID uh two. 17:28.420 --> 17:34.760 Now, you may ask me, am I sure that this is work in the kitchen with the table? 17:34.850 --> 17:37.070 Yes, it's actually complicated with the table. 17:37.100 --> 17:38.780 How can you be able to confirm that? 17:38.780 --> 17:44.120 Let's try to delete some of the house physics. 17:44.210 --> 17:50.060 Remember, physics has a better history and physics and want to delete that. 17:50.060 --> 17:53.090 Now if we want to delete that, we're going to get an error. 17:53.120 --> 17:57.170 Go ahead and delete whatever we have in here because we have a certain distance. 17:57.170 --> 17:59.180 And then I'll go ahead and say. 18:02.300 --> 18:07.430 Delete from teacher. 18:07.430 --> 18:15.770 So I'm trying to delete that from teachers table where only we are close where. 18:18.410 --> 18:22.310 Teachers ID is equals to two and a semicolon. 18:22.310 --> 18:25.190 So try to delete this and let's see if that works out. 18:25.220 --> 18:26.720 Go ahead and hit on these. 18:26.720 --> 18:34.770 And on down here we have an error and it says cannot delete or updates a parent's rule. 18:34.800 --> 18:37.770 A foreign key constraint falls on the here. 18:39.090 --> 18:46.830 Can you see that we now we cannot be able to delete that because this table or this column on that teachers 18:46.860 --> 18:54.000 a on teachers table, which is known as teachers ID, has been linked to the subjects table. 18:54.750 --> 18:58.920 So we want to do more on this is very interesting on using the foreign key. 18:58.950 --> 19:01.200 So we're going to do on this and practice more. 19:01.200 --> 19:04.050 So go ahead and practice whatever you've learned here. 19:04.050 --> 19:09.060 And we kick off again in the next lecture is going to be a choice team is having a question that is 19:09.060 --> 19:11.340 redirect to use the question and answer section. 19:11.340 --> 19:13.560 And I'm going to get back to you as soon as possible. 19:13.590 --> 19:14.490 Thank you so much. 19:14.490 --> 19:17.280 And I'm going to see you in the next video lecture.