Parent and child table in SQL with code example
- Last Updated : 26/06/2021
In MySQL to make an entity unique, we need to use a primary key also to make a relationship between two entity, that means to table we need to use a foreign key. so in this post, we will learn how can we create a table with a primary and foreign key.
Before going to the coding example and let's learn fast what is the primary key? and what is the foreign key?
A primary key is an attribute that uniquely identifies an entity.
Rules of the primary key
- ✅ A primary key must be unique
- ✅ A primary key cannot be null
- ✅ A table can have only ONE primary key
A table’s primary key is the foreign key in another table.
SQL Parent-Child Relationship
If the table contains a foreign key then this table will be called a child table.
If a table contains the primary key then it will be called a parent table for reference table.
Here we see the books table contains the author table’s primary key(A_ID) so in this case, the author table is our parent table and the books table is our child table.
Code example of parent-child table in SQL
CREATE TABLE author( A_ID int NOT NULL, Name varchar(100), PRIMARY KEY(A_ID ) ) CREATE TABLE books( B_ID int NOT NULL PRIMARY KEY, Name varchar(100), Price int NOT NULL, A_ID int FOREIGN KEY REFERENCES author(A_ID) );Here A_ID used as foreign key in the books table, which is the primary key in the author table.
Still you face problems, feel free to contact with me, I will try my best to help you.