How to add records in self referencing table?

Question

I am a noob in MySql. I want to create the following self-referencing table:

EMPLOYEE
+-----+------+------+
|Name |E-ID  |M-ID  |
+-----+------+------+
|ABC  |12345 |67890 |
|DEF  |67890 |12345 |
+-----+------+------+

I use the following commands:

CREATE TABLE EMPLOYEE (
    NAME VARCHAR(20) ,
    E-ID CHAR(6) NOT NULL ,
    M-ID CHAR(6) NULL ,
    PRIMARY KEY (E-ID) ,
    FOREIGN KEY (M-ID) REFERENCES EMPLOYEE(E-ID)
    );

Now my problem is, how do I enter the two records? I mean, each time the foreign constraint will fail. I tried entering:

INSERT INTO EMPLOYEE VALUES('12345','67890');

I also tried :

INSERT INTO EMPLOYEE VALUES('12345','67890'),('67890','12345');

Both of the above commands fail. Giving error:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails BLAH BLAH

Guys, actually I was trying to implement the tables given in slide number 25 of the following ppt: The Relational Data Model and Relational Database Constraints

The constraints are:

SUPERSSN Of EMPLOYEE references SSN of EMPLOYEE.
MGRSSN of DEPARTMENT references SSN of EMPLOYEE.
DNO of EMPLOYEEE references DNumber of DEPARTMENT.

After I have created the tables, how do I add records? It will always fail the foreign key constraints.

The data that you have given us is recursive - ABC is managed by DEF (that's ok), but DEF is managed by ABC! The M-ID field of DEF should be NULL (or some other employee). Theoretically, the only record where M-ID is NULL is for the CEO. Filling the table is going to be problematic unless you start at the top (CEO), then add a layer of senior managers, then a layer of managers who are managed by the senior managers, etc. — No'am Newman, Sep 19 '12 at 08:25

score 4 · Accepted Answer · 2012-09-19T07:31:00.270

4

As MySQL does not support deferrable constraints (which are the "natural solution" to such a problem) you will need to do this in two steps;

INSERT INTO employee (name, `E-ID`) values ('Arthur', '123456');
INSERT INTO employee (name, `E-ID`) values ('Ford', '67890');
UPDATE employee 
    SET `M-ID` = '67890' 
WHERE `E-ID` = '123456';

UPDATE employee 
    SET `M-ID` = '123456' 
WHERE `E-ID` = '67890';

You circular reference does sound strange to me though. An employee being the manager of an employee who is in turn his manager?

Allow me two comments on your table definition:

avoid column (or table names) with special characters that need quoted identifiers. Using E_ID instead of E-ID will save you some trouble in the long run
If your employee ID can be shorter than 6 characters than you most probably want to use VARCHAR(6) instead of CHAR(6) due to the padding of the values with the CHAR datatype.

edited Sep 19 '12 at 07:31

answered Sep 19 '12 at 07:24

You're missing the second update - update employee set m-id = '12345' where e-id= '67890'. – No'am Newman Sep 19 '12 at 07:28
@NehalJ.Wani you still need to do it that way: insert a NULL then update later - or upgrade to a modern DBMS that supports deferred constraints. – Sep 19 '12 at 08:17
@No'amNewman Newman Why not use this: SET FOREIGN_KEY_CHECKS = 0; Then populate the table and then SET FOREIGN_KEY_CHECKS = 1; – Nehal J Wani Sep 24 '12 at 19:56

score 2 · Answer 2 · answered Dec 02 '15 at 01:59

2

There are good answers here, but figured I'd point out the quick-and-dirty:

SET FOREIGN_KEY_CHECKS = 0; 
INSERT INTO EMPLOYEE VALUES('12345','67890'),('67890','12345');
SET FOREIGN_KEY_CHECKS = 1;

answered Dec 02 '15 at 01:59

redreinard

1,994
1
19
25

score 1 · Answer 3 · answered Sep 19 '12 at 07:17

It will obviously fail because the table is empty.

INSERT INTO EMPLOYEE VALUES('12345','67890');

Since M-ID depends on E-ID. Remove the constraint so you can insert record. The best thing you do is to create another table for M-ID and reference it to Employee table.

How to add records in self referencing table?

3 Answers3