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.
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
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.
IF NOT EXISTSensures that table would only be created if it doesn’t already exists in the MySQL database.
CONSTRAINT constraint_nameis used to give name to the Foreign Key constraint. If we exclude it then MySQL automaticaly generates a name for the constraint.
ON DELETEare 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_actioncould 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).
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.
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) ;
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.