I welcome you back again to another video lecture in MySQL.
And in this video lecture we go ahead and look at foreign keys.
So in MySQL we have what is called foreign keys.
And this helps us to be able to link to tables.
So if we want to communicate between different tables you go ahead and use the foreign keys.
Because this foreign key helps you to be able to interact between two tables.
Let's go ahead and say we have a table called Teachers Table.
And we have another table called the subjects table.
And a subject table is going to be linked to the teachers table so that our teacher communicated with
different courses or different subjects.
Adaptation.
Let me say I have a teacher called uh Steve and the teachers table, and we have a subject called chemistry
and the Subjects table.
So on the teacher's table, our judge is going to be communicating with chemistry in the subjects table.
So the teachers table and the subjects table will always interact.
So each teacher in the teacher's table.
Always have a subject assigned to them in the subjects table.
So Judge Steve MLA and global we have different subjects like chemistry, biology, physics or history
respectively assigned to them.
And these are two different tables.
So how are we to do this.
So first of all let's go ahead and create a table called the Teachers Table.
And then we'll go ahead and create a table called the subjects table.
And we'll see how we can be able to create a foreign key that's going to interact between these two.
Let's go ahead and create a table called dialogue.
Create a table.
And the name of the table will be teachers.
And this is the first table we are creating warhead and put a semicolon at the end of this.
Then we go ahead and, uh, create the teachers ID.
Teachers underscore ID and, uh, we just need the teachers ID and their first name.
So go ahead and add your last name.
Anything you want to add under this table there's no problem.
So the name is going to the teacher ID is going to be an integer data type.
And then we're going to give you the primary key.
And let's also make this to be autoincrement.
So the teacher ID is integer data type primary key and auto increments.
So the next one we need now is the first name.
Just the first name only.
And it's going to be varchar.
That is what the data type is going to be.
And we go ahead and give it to be dosed and put that is all.
So you can learn how to select these.
If you want to populate your table and say that, go ahead and say select all from teachers and press
semicolon at the end.
And let's go ahead and query this table.
And right now you can go ahead and refresh your database.
And you can see we have a test table.
And if you go to foreign keys no nothing is there.
If you go to index you can see we have one primary key there.
And under the column we have our teachers ID and our first name.
All right.
So that is for teachers ID.
Now let's go ahead and uh insert some rows into this.
So under the first name and uh we go ahead and insert the teachers names.
Let's go ahead and do that.
So all I need to do right now is uh let's go ahead and insert Into.
Into chess.
And there we go ahead and put only the first name.
The reason is because we have made the id to be also increment, and therefore we don't need to specify
that.
Again go ahead and say values and I'll go ahead and add that.
So I just want to add only four of these.
The comma then.
A comma and then press semicolon.
At the end of this only value.
First name I'm going to add a is going to be judge.
So the first name is going to be judge.
Then the second name.
Is Steve Then the next round is a normal one and the next one is global.
All right.
So this is where I want to add.
And I'm going to use the select to populate that.
You can be able to see that this guy had run this and check it out.
So basically it's a query and we see the first name and the teachers ID we are automatically incremented
for us.
So now we have never to create the teachers table.
What we need.
Now we're going to link the teachers table to the subjects table.
So we're going to start by creating the subjects table.
And this is where we're going to add a foreign key to link to the teachers table we just created.
Now we'll go ahead and remove everything we have right in here.
And then we'll go ahead and create a new table.
And I'm going to say create a table.
And the table name is going to be subjects.
And then we go ahead and press semicolon at the end.
Now we go ahead and put in some columns.
Subjects underscore ID that is your first one because you need to have an ID and that's going to be
int the data type.
Then let's also make this to be the primary key.
Primary key.
And also auto increment the camera.
And then we're going to put in the subject name because these subjects are going to have a name.
Subjects underscore name.
All right.
So this is going to be just varchar.
And then we go ahead and make that to be just 15.
Then after this we now need to create a column that's going to hold the teachers ID.
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
any name.
So you can call out to me any name.
Let's go ahead and say that this is a teachers ID.
So go ahead and say.
Teachers underscore ID and then it's going to be just int.
All right.
Now put a comma.
Then we are going to point that to the teachers table using the foreign key constraint.
So go ahead and see.
Foreign key.
And then we are going to link.
The foreign key is going to be link to the column we have right here called the teachers.
Uh ID.
And we're going to also reference that.
It's It refreshes toward a table known as a sequence, and the table is also now known as a teacher's
ID.
All right.
So now we have this.
Let's go ahead and select all.
Select all from subjects after a semicolon at the end.
So I'm able to see our table.
And this is the cutest query.
And now we have this table called subjects.
And it has subjects ID subject name and teachers ID.
Let's go ahead and refresh this and check it out.
Now I can still have a table called subjects.
Go ahead and open up this table.
Then under the column we have subjects subject name and teachers ID.
Then under the index check this out.
We have the one called primary key.
And we have teachers ID.
Remember that we did not specify any ID as a primary key right in here.
Do you know what happens?
It actually refers this to the teacher ID we created a teacher ID is also a primary ID is a primary
key.
That was a constraint.
A primary key constraint was also added to that id.
So the teacher id was created in the lab table on that.
Teachers has a primary key constraint to eat.
And another important thing which is not available under this teacher table which we have here is under
the following tables have no foreign keys, but under the subjects go under the foreign keys.
We have subjects underscore id f key underscore one.
So you can also see now we have a foreign key now.
And that is very very very interesting.
So if you want to drop your foreign key is very simple.
You can go ahead and see drop.
And um Okay, this was a full table before we drove.
So you can see the table.
And the 212 is our subjects.
And then we go ahead and uh drop.
A foreign key.
And then we're going to give the name of the foreign key which will have a subjects point and say this
is a.
Subjects underscore IB.
F key then underscore one.
Then put a semicolon at the end.
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
and check the foreign keys.
So guide and quizzes.
So under the foreign keys we have no foreign key here.
So that is gone.
Our foreign key is gone.
I want Eve, we go out on the water again and add the foreign key back and given it a unique name.
Maybe give it our own name.
Maybe not the name that is day.
The one without the night.
We create a new foreign key and give it a new name.
So to do that you go ahead and say add constraint.
So add a constraint.
We give the name of the constraint as Guyana say it is a fk underscore.
Subject underscore okay.
Sorry say underscore because it's the ID that we need.
So underscore ID alright.
Now we can go ahead and see foreign key then.
So foreign key we go ahead and put in the US ID that is what is needed.
So what I need to do here is to say, okay, this is going to be in a lower case.
Teachers underscore ID because this is teachers underscore ID.
And that will go ahead and um a references that to the teachers table.
And uh the column is still teachers underscore ID and that is all.
Go ahead and place semicolon at the end of this.
And let's go ahead and execute this right now.
Let's go ahead and refresh this again and check it out.
So one of the foreign keys you can see we have uh FCPS teacher underscore ID.
And that is what we just created.
So if you have an existing table you want to impute it.
Just add is how it can be able to do that.
So the excess of this is for us to actually know how to create this from the beginning, and how to
add that to an existing table.
Go ahead and remove everything right here.
Now what I had on the deletes all the data we have on the subject so I can be able to create our own.
So let me go ahead and say delete a from subjects and put a semicolon at the end.
And let's go ahead and drop whatever we have right in there.
And this is what we have.
So I just want to explain that before we can continue.
But before we continue, I want to set the subject ID to start from 101, because our teachers table
starts auto increments from one, and I want to increment or auto increment the subject ID from to start
from 101.
That is, I want to have a chemistry 101, physics 102 and so on.
I hope that is cool.
Oh, I Rabbi, sorry, can alter our autoincrement from anywhere you want.
So let's go ahead and say, um, alter table.
And in all the table is going to be subjects and then we'll go ahead and, uh autoincrement.
And uh, let us start from 101 because I want that to have a class code 101, 102, 103 and go ahead
and do this.
Now we have a query, and then we can now go ahead and insert some rows into that.
Now let's go ahead and remove this.
And I want to go ahead and insert some rules.
Go ahead and say insert into subject.
Then I just going to insert this subject name and the teachers ID.
All right.
Now go ahead and, uh, say I want to just input.
Subjects.
Subject name.
Then the next one is going to be the teachers ID.
Now, the reason why I'm inputting teachers ID is because I don't want the teachers ID to auto increment
in the subjects table, because I can assign any subject to any teacher by just inputting the teacher
ID, okay.
And I want whenever I put a one on one, it'll automatically go to teacher with the ID one.
You understand.
So that is why I'm doing this.
I must auto incrementing the teachers ID under the subject.
I hope that is understandable.
Now let's go ahead and our input values.
And let's go ahead and input comma.
So we have our teachers.
So I'm going to add a for subjects.
The comma.
A comma.
And put a semicolon at the end.
Now let me say there for subject ID the one I have here is going to be.
Physics then.
Then I'm going to say physics is going to be assigned to teacher with the ID two.
All right.
Now the next one is going to be.
Chemistry.
And I want to assign that to teacher with ID one.
You see I just want to alter that.
Just making it to be teacher one first or physics to go to teacher one.
Chemistry to teacher two.
So I just want to manually add the teacher's ID on that subject.
Now we go ahead and say our biology.
And put a let me say it goes to the chat three and, uh.
History.
The message history goes to chat two as well.
So you can see I signed a two.
Subjects to one teacher.
You see that.
So here is possible because the teacher's ID on right here is not Autoincrement analyst.
Go ahead and execute this and check it out.
And you can see I have a physics Autoincrement 101 physics 101 and assigned to teachers ID two and physics
one, chemistry 102 assigned to teacher ID one and uh biology one two, three assigned to teacher ID
three and history is a 104 is assigned to teacher ID uh two.
Now, you may ask me, am I sure that this is work in the kitchen with the table?
Yes, it's actually complicated with the table.
How can you be able to confirm that?
Let's try to delete some of the house physics.
Remember, physics has a better history and physics and want to delete that.
Now if we want to delete that, we're going to get an error.
Go ahead and delete whatever we have in here because we have a certain distance.
And then I'll go ahead and say.
Delete from teacher.
So I'm trying to delete that from teachers table where only we are close where.
Teachers ID is equals to two and a semicolon.
So try to delete this and let's see if that works out.
Go ahead and hit on these.
And on down here we have an error and it says cannot delete or updates a parent's rule.
A foreign key constraint falls on the here.
Can you see that we now we cannot be able to delete that because this table or this column on that teachers
a on teachers table, which is known as teachers ID, has been linked to the subjects table.
So we want to do more on this is very interesting on using the foreign key.
So we're going to do on this and practice more.
So go ahead and practice whatever you've learned here.
And we kick off again in the next lecture is going to be a choice team is having a question that is
redirect to use the question and answer section.
And I'm going to get back to you as soon as possible.
Thank you so much.
And I'm going to see you in the next video lecture.