declare

MySQL tutorial 18 - Foreign Keys



Sharing buttons:

hey you do buzzer goin its Quintin here

and welcome to tutorial number 18 and

this is probably going to be the last

tutorial of the series and what I wanted

to talk to you guys about is something

called a foreign key if you guys have

been following along with my tutorials

then you'll know that through some of

the tutorials where I spoke about

joining different tables

I actually made reference to a foreign

key and I kind of explained what it is

but let's just do a really quick recap

so let's go over to the users table and

you can see that over here in the users

table I have an ID column so if I browse

that each one of these users has an ID

and basically in this table this is a

primary key right but if I make use of

this exact same column or this exact

same information in another table that's

when it becomes a foreign key so let's

go over to the addresses table and if I

browse this you can see I'm making use

of that exact same information over here

to link back to a specific user right so

now our users ID column is actually a

foreign key to the users table we're

linking back to that information and

that's really useful because as you guys

saw over the past couple of tutorials it

allowed us to take that addresses table

and that users table and join those two

tables together and share the

information between those two tables so

then I could see what address belong to

what specific user

now something I didn't do for you guys

was that I didn't declare the foreign

key so yeah you'll notice one key

difference between my table right now

and your tables if you're looking at the

same tables I created is that mine have

these little blue links so I can click

on them and then it actually takes me

through back to the users table and to

that specific user right and that's

something that I didn't set up for you

guys but I'm going to show you guys how

to set it up now so I think for this the

purpose of this tutorial let's work with

the orders table and I'm just going to

click browse and you can see right now

none of these are linkable at the moment

and that's essentially

the only advantage that declaring a

foreign key is really going to have is

that if you're using this in phpMyAdmin

you can click on it and can actually

link to that specific table so this will

if we so so if I declare this as a

proper primary key I'll be able to click

on this link and basically link back to

the products table and we'll see all the

information about that specific product

right so there are a couple of different

ways to declare your foreign key and

let's go over to the SQL tab so I can

actually start running some queries so

let's just delete everything that's in

here now the first way to declare a

primary key is when you originally

create your tables I'm just going to

copy and paste some code in here this

code doesn't really relate exactly to my

tables but you'll get the picture right

so if you guys already know how to

create tables hopefully you do because

that was a tutorial that we did a couple

videos back but yeah you know how to

create different fields in your table

and you know how to create a primary key

so basically adding a foreign key is

just one extra line in that query and

basically you just declare your foreign

key so use those two key words then

select the column within the table

you're creating that you want to be a

foreign key and then say which other

table you're referencing so in this case

if I were referencing information from

the product table I'd put products here

and then I put the product ID here

basically

alright so that's just where you would

add that extra line that's what you kind

of needed to know for this bit if you

don't really understand it properly then

let's basically show you guys a more

practical example that does relate to

our tables so instead of creating a

table what I'm going to do is alter the

orders table and yeah I'm going to make

all of these actually actually linkable

so let's start with the Product ID and

essentially what I want to do here is

type in a

there is a altar table and then we want

to put in the name of the table that we

want to alter which in this case is

going to be orders and yeah now you want

to add a foreign key so let's add

foreign key and now all you need to do

is put in the column that you want to be

a foreign key which in this case I said

let's start with the product ID first

let's turn caps lock off and hit product

ID into those brackets or into those

parentheses and now we want to just say

which table this is referencing so let's

add in the keyword references and the

product ID is actually referencing from

the products table so let's put in the

product tables name and now let's put in

the ID of that products table right so

product ID now if I copy this and hit go

you can see the query has run

successfully and I don't really have any

other information in here so let's just

go back to the orders table to make sure

that this worked so that's it browse and

you can see that now all of these are

linkable and if I click on this link I

should see my product with the ID of

product 2 and you can see this does have

an ID of product 2 and it is a blue

lightsaber right so we can essentially

do the exact same thing for the next

couple of columns so let's go back to

orders and browse so the next two

columns are user ID and address ID so I

think let's just do the user ID real

quick and then I'll move on to the next

point so again it's literally the exact

same thing but in this case instead of

linking the product ID I want to link

the user ID so this is the name of the

field in the current table that I'm

altering and then we just need to grab

the users table because that's the table

we're referencing and the name of the

column there is actually just ID so if

you browse the users table then you'll

see that all right but let's hit go

and again you can see the query ran

successfully and pretty much everything

kind of just disappeared so if you

really want confirmation that that

actually worked

go over to the orders table and look and

it is now linkable so if I click on this

I should see user with ID of 14 and

that's exactly what happened I've got

the user with the idea 14 which happens

to be Luke Skywalker whoo right so that

works but at some point in time you

might actually want to remove a foreign

key I can't really think of a proper

example of why you would want to do that

but yeah let's just say you want to

remove one of these foreign keys if you

look through any information on the

Internet

you're probably going to land up seeing

people advise you to do this so they'll

say also table the table you want to

alter and then instead of add foreign

key the next part of the query kind of

looks like this it's drop foreign key

and in the name of the key that you want

to drop without brackets right so that's

kind of what you'll see all over the

internet when you want to actually look

for information on how to drop a foreign

key the only problem is if you hit go

you're probably going to get this error

which is the era of rename learning

overhear orders so it's like trying to

rename our table for some reason

whatsoever I don't know okay and so

which one do to get around this error is

actually go over to the orders table and

go to the relational view so that's

under structure relational view and over

here you can see that we have orders

underscore IVF k2 and that is our user

ID column and that is a foreign a

foreign key for the users ID right same

thing over here

IVF k1 is a for the products ID and that

makes our product ID a foreign key so

that means that when we write our query

to drop one of these foreign keys

instead of typing in the name of the

foreign key that you originally created

which was product idea whatever you

actually want to grab one of those names

so it was orders

underscore IB FK underscore 1 to drop

the product ID or underscore i2 to drop

the user ID so let's just drop

underscore one so take go and now that

that query has run successfully I kind

of don't really have any information

over here to see that a trans excess

leaked it disappeared really quickly so

what I want to do is hit the orders

table and go back to browse and you can

see that now the product ID has been

unlinked so basically what we did was we

killed the link between the products

table and the orders table the values

are still here

like we didn't delete the actual values

we just killed the link between the two

and so that is how you create a foreign

key or delete a foreign key from a table

and yeah hopefully you found this

tutorial informative and helpful and

before I end of this video I just want

to send a huge shout out to these guys

so these guys are my patrons who

contribute five dollars or more every

single month on patreon and if you like

the videos I create you want to help me

make more of these videos more often

then consider becoming a patron consider

sending over a little bit of money

because that's going to help me create

more time to make videos for you guys

and yet even if you guys can't help

financially then just leave a like hit

that subscribe button if you haven't

already shared this video because that's

going to help my channel grow and I'll

see you guys next time