MySQL 8.0.22 | How to create Foreign Key

Hi guys, so far it wasn’t that complicated, but from today it’s going to be slightly difficult. But once you get the basic concept of Foreign Key, you’ll be fine. Let’s get this started!

1. Why do we need Foreign Key?

Let’s say there’s a member information.

Here, ‘carNum’ means car plate number. You see there’s two people having one car each. There’s nothing wrong until ‘Nina’ gets another car. Some might think, “Easy, you can just put new tuple!”. But there’s a major problem of that idea.

Do you see what’s the problem? Right, since id is primary key(PK), there will be an error, if you put the tuple that has same value with another. The best way to do it is separating the table into two different tables.

Just like this. Now the only thing we need to know is how to connect these two tables. This is when we use the ‘Foreign Key’.

2. Create Foreign Key

It’s very easy to create Foreign Key. I’ll create the exact same tables with above. The name of the first table is member, and we can also call this original table as the ‘parent table’. Creating parent table is just same like creating normal ones.

create table member(
id varchar(3) primary key,
name varchar(5),
phone varchar(4),
email varchar(7),
point int
);

But here, the column which is going to be connected with the second table, has to be unique. So I put ‘primary key’ in id column. If you don’t know about unique and other constraints or key, take a look at my last posting down below!

MySQL 8.0.22 | Constraint and Key (Unique, Not Null, Default, Primary key)

Next the second table which is named car, and you can call this table as the ‘child table’. We’re gonna connect parent an child table’s id column, and the query is just like below.

create table car(
id varchar(3),
carNum varchar(4),
foreign key(id) references member(id)
);

After write all the columns you need down, and then put , foreign key(childTable_column) references parentTable_name(parentTable-column) .
This is how you use foreign key.

+Let’s checkout if we made the tables right

Just in case, I’ll put some tuples, if there’s an error coming up.

insert into member values
('AAA','Nina','1111','a@a.com',30),
('BBB','Tom','2222','b@b.com',60);
insert into car values
('AAA','1234'),
('AAA','9101'),
('BBB','5678');

I put the same tuples like the graphs above. And as always we’ll check the table with the ‘select’. In this case we need to use the special select that can call two tables at the same time. and it goes like
select * from parentTable, childTable where parent.column=child.column;
I know it’s not easy, but don’t panic, cause I’m gonna talk about this more in my further postings.

mysql> select * from member, car where member.id=car.id;
+-----+------+-------+---------+-------+------+--------+
| id  | name | phone | email   | point | id   | carNum |
+-----+------+-------+---------+-------+------+--------+
| AAA | Nina | 1111  | a@a.com |    30 | AAA  | 1234   |
| AAA | Nina | 1111  | a@a.com |    30 | AAA  | 9101   |
| BBB | Tom  | 2222  | b@b.com |    60 | BBB  | 5678   |
+-----+------+-------+---------+-------+------+--------+
3 rows in set (0.00 sec)

Great! We did it without causing any error.