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:

1. SUPERSSN Of EMPLOYEE references SSN of EMPLOYEE.
2. MGRSSN of DEPARTMENT references SSN of EMPLOYEE.
3. 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.

Answer 1

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.