How do I maintain Referential integrity for a foreign key in the same table in MySQL?

Question

How do I add referential integrity within columns of the same table in MySQL?

I have a table with the following structure:

Employee
-----------------------------
----[Other Columns Snipped]----
super_serv_no       
serv_no            (FK for super_serv_no)

How do I make it so serv_no is a foreign key to super_serv_no, and enforce referential integrity?

This related question would probably be helpful to you: http://stackoverflow.com/questions/675037/how-do-i-add-a-foreign-key-pointing-to-the-same-table-using-myphpadmin — Joseph B, Apr 17 '14 at 15:57

score 0 · Answer 1 · answered Apr 17 '14 at 16:03

This is an example of syntax that would create a foreign key constraint like the on you describe (for table using InnoDB engine):

ALTER TABLE `employee` ADD CONSTRAINT 
  `FK_employee_head_serv_no` 
  FOREIGN KEY (`head_serv_no`) REFERENCES `employee` (`serv_no`) 
  ON DELETE RESTRICT ON UPDATE CASCADE

The normative pattern is for a foreign key to reference the PRIMARY KEY of the table. (I think MySQL is more relaxed, and a foreign key can reference any set of columns that is indexed, so at a minimum, you'd need to have an index with a leading column of serv_no.) We'd normally have a unique index on just the serv_no column.)

score 0 · Answer 2 · answered Apr 17 '14 at 16:04

You can define the FK just similarly to definiing it for a different table:

ALTER TABLE Employee
    ADD CONSTRAINT FOREIGN KEY (super_serv_no)
    REFERENCES (serv_no);

Note: Make sure that super_serv_no is not defined as NOT NULL. This is because the topmost serv_no will not have a super_serv_no.

References: Using FOREIGN KEY Constraints at MySQL 5.0 Reference Manual

How do I maintain Referential integrity for a foreign key in the same table in MySQL?

2 Answers2