Self Referencing Foreign Key in MySQL [Explained]

In our previous post, we did not discuss Self Referencing Foreign Key while talking about Foreign Key in MySQL. So let’s see what actually is self-referencing Foreign Key in MySQL and why it is used?

what is Self Referencing Foreign Key?

Sometimes Foreign Key of a table references back to the primary key of the same table. In that case, the Foreign Key is said to be self-referencing.

Example:

In this table, the ’employee_id’ column is the primary key and the ‘manager_id’ is the foreign key.

Each employee reports to their manager who is also an employee of the same company.

Hence instead of creating a separate table for managers, we can make use of the self-referencing foreign key to define the relationship between an employee and manager on the same table.

These are some of the prominent properties of the MySQL self-referencing foreign key constraint

• Self Referencing Foreign Key is also known as Recursive Foreign Key.
• It defines the relationship between the same entity or table.
• and all others properties of normal foreign key constraint in MySQL.

Create a Table with Self Referencing Foreign Key

The general syntax to create a new table with self-referencing foreign key is

CREATE TABLE [IF NOT EXISTS] table_name(
   column_1_definition,
   column_2_definition,
   ...,
   [CONSTRAINT constraint_name]
   FOREIGN KEY (column_name, ...)
   REFERENCES table_name(colunm_name,...)
   [ON DELETE referential_action]
   [ON UPDATE referential_action]
);

Note: Syntax within square brackets [] are optional.

where,

• IF NOT EXISTS ensures that table would only be created if it doesn’t already exists in the MySQL database.
• CONSTRAINT constraint_name is used to give name to the Foreign Key constraint. If we exclude it then MySQL automaticaly generates a name for the constraint.
• ON UPDATE and ON DELETE are subclauses of the FOREIGN KEY clause which defines what referential action should take place in case of an UPDATE or DELETE operation on a key value in the parent table that has matching rows in the child table.
• referential_action could include CASCADE, SET NULL, RESTRICT, NO OPTION and SET DEFAULT.

Notice the referencing table name is the same as the name of the table which we are creating (i.e table_name).

Example:

CREATE TABLE employee (
employee_id int PRIMARY KEY, 
employee_name varchar(30), 
salary varchar(30), 
manager_id int, 
CONSTRAINT sr_fk_emp_man FOREIGN KEY (manager_id) REFERENCES employee(employee_id)
);

Add Self Referencing Foreign Key to an Existing Table

Similar to the earlier syntax, the general syntax to add a self-referencing foreign key to an existing table in MySQL relational database is

ALTER TABLE table_name
   ADD [CONSTRAINT constraint_name]
   FOREIGN KEY (column_name, ...)
   REFERENCES parent_table(colunm_name,...)
   [ON DELETE referential_action]
   [ON UPDATE referential_action]
;

where syntax inside square brackets [] is optional.

Recommended: Primary Key Constraint in MySQL

Here is the example where we are first creating the table then adding the foreign key constraint to it.

Example:

CREATE TABLE employee (
employee_id int PRIMARY KEY, 
employee_name varchar(30), 
salary varchar(30), 
manager_id int
);

ALTER TABLE employee
   ADD CONSTRAINT sr_fk_emp_man 
   FOREIGN KEY (manager_id)
   REFERENCES employee(employee_id)
;

The employee table now has a foreign key column: manager_id which refers to the primary key column: employee_id.

To insert records in these types of tables, we first insert records without the foreign key values.

INSERT INTO employee (employee_id, employee_name, salary) VALUES 
(21, 'ISACC NEWTON', 732611),
(32, 'ROMEO', 329827);

employee_id	employee_name	salary	manager_id
21	ISACC NEWTON	732611	null
32	ROMEO	329827	null

Then we add the foreign key to the new records by updating the manager_id field with the value of the employee_id of another record of the table.

UPDATE employee SET manager_id = 21 WHERE employee_name = 'ROMEO';

employee_id	employee_name	salary	manager_id
21	ISACC NEWTON	732611	null
32	ROMEO	329827	21

When you add a foreign key constraint to a table using ALTER TABLE, remember to first create an index on the column(s) referenced by the foreign key.

I hope the concept of the self-referential foreign key is now clear to you. If you have any doubts or suggestions then please comment below.