Parent and child table in SQL with code example

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.

Parent and child table in SQL

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.

SQL Parent-Child Relationship

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.