In our previous post we did not discussed about 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:

Self Referencing Foreign Key

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

Each employee reports to its 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

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 same as the name of the table which we are creating (i.e table_name).

Example:

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

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:

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 suggestion then please comment below.

Leave a Reply