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?

Primary 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

Foreign key

A table’s primary key is the foreign key in another table.

SQL Parent-Child Relationship

Child table

If the table contains a foreign key then this table will be called a child table.

Parent 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.