SQL Foreign Key

Filed Under: SQL

When a database table is designed an important factor is to make sure that the table is scalable enough and is normalized at the same time. In order to make sure that the tables are well linked and normalized we use SQL foreign keys.

SQL Foreign Key

A foreign key is defined by a column that matches a column of another table. The foreign key constraint is used to make sure that data referential integrity is maintained.

In this tutorial, we will try to understand the following topics.

  1. Create a foreign key
  2. Add a foreign key
  3. Drop foreign key

1. Create a Foreign Key

The below-mentioned syntax will help in understanding how to create a foreign key in the child table. A child table is a table where the foreign key is defined from the parent table.

Syntax: –

CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action

Please find below the details of the syntax mentioned above,

Constraint: – This keyword allows to specify a name for the foreign key constraint.

Foreign Key: – This keyword specifies the columns in the child table that refers to the primary key of the parent table.

References: – It specifies the parent table and its column that is used as a foreign key.

On Delete: – On delete specifies the action that will be performed on the deletion of a row of the parent table.

On Update: – On update specifies the action that will be performed on the updating a row of the parent table.

We will create a new table using the following

CREATE TABLE school(
sch_id int not null auto_increment primary key,
sch_name varchar(255) not null,
sch_description text
) ;

CREATE TABLE student(
std_id int not null auto_increment primary key,
std_name varchar(355) not null,
std_class varchar(355) not null,
sch_id int not null,
FOREIGN KEY fk_sch(sch_id)
REFERENCES school(sch_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
);

Using the above query we will be creating two tables School and Student. School is the parent table for the Student table and has a foreign key reference for the same.

After executing the above query following will be the property of the Student table.

Add Foreign Key MySQL

Add Foreign Key MySQL

2. Add a Foreign Key

The below-mentioned syntax will help in understanding how to add a foreign key in the child table. Using Alter keyword we can add a foreign key to an existing table.

Syntax: –

ALTER table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action

We will try to add a new foreign key to the Student table which we have created in the previous example.

CREATE TABLE class(
cls_id int not null auto_increment primary key,
cls_name varchar(255)
);

ALTER TABLE student
ADD COLUMN cls_id int not null;
ALTER TABLE student
Add FOREIGN KEY fk_cls(cls_id)
REFERENCES class(cls_id)
ON DELETE NO ACTION
ON UPDATE CASCADE;

Using the above query we will be creating a new table Class and will be adding a foreign key to the Student table

After executing the above query following will be the property of the Student table.

Alter Foreign Key MySQL

Alter Foreign Key MySQL

3. Drop a Foreign Key

The below-mentioned syntax will help in understanding how to drop a foreign key in the child table. Using Alter keyword we can drop a foreign key to an existing table.

Syntax: –

ALTER table_name
DROP FOREIGN KEY constraint_name;

We will try to drop a foreign key from the Student table which we have created in the previous example.

ALTER TABLE student
DROP FOREIGN KEY fk_cls;

Using the above query we will be dropping the foreign key of Student table

After executing the above query following will be the property of the Student table.

Drop Foreign Key MySQL

Drop Foreign Key MySQL

How Foreign Key On Delete Cascade Works?

We can use the following query to set the “ON DELETE CASCADE” option for the foreign key constraint. In this case, if the corresponding row in the parent table is deleted, the rows in the child table will also get deleted.

Let’s add it to the student table.

ALTER TABLE student Add FOREIGN KEY fk_sch(sch_id) REFERENCES school(sch_id) ON DELETE CASCADE;

We will run the following command to insert data into School table.

School Table:


INSERT INTO `test`.`school`
(
`sch_name`,
`sch_description`)
VALUES("TestSchool1","School for test 1"),("TestSchool2","School for test 2"),("TestSchool3","School for test 3");

Student Table:


INSERT INTO `test`.`student`
(
`std_name`,
`std_class`,
`sch_id`,
`cls_id`)
VALUES("Student 1","Class 1",1,1),("Student 2","Class 1",2,1)("Student 3","Class 1",1,1),("Student 4","Class 1",1,1),("Student 5","Class 1",3,1),("Student 6","Class 1",3,1);

Now we will try to delete a row from the child table using the following command.


DELETE FROM `test`.`student`
WHERE sch_id = 2

This will only delete the entry from Child table where sch_id = 2 and the parent table will remain unchanged.

Student Table After Delete

Student Table After Delete

Now we will try to delete an entry from the parent table and we will see the changes on child table for the same.

We will execute the following command for delete row from the parent table.


DELETE FROM `test`.`school`
WHERE sch_id=3;

Following will be the result post execution of delete command for the parent table. The rows in the child table will also get deleted.

Student Table After Delete Sch Id 3

Student Table After Delete Sch Id 3

Conclusion

SQL Foreign keys help us in creating a relationship between tables. It also lets us specify what will happen if the foreign key in the parent table is updated or deleted.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages