Restriction on self referencing on insert in MYSQL?

Question

How to restrict insert on adding self-referencing rows in a recursive relation table (a table contains foreign key points itself)?

mysql> SELECT * FROM Employee;
+-----+------+-------------+------+
| SSN | name | designation | MSSN |
+-----+------+-------------+------+
| 1   | A    | OWNER       | NULL |
| 2   | B    | BOSS        | 1    |
| 3   | C    | WORKER      | 2    |
| 4   | D    | BOSS        | 2    |
| 5   | E    | WORKER      | 4    |
| 6   | F    | WORKER      | 1    |
| 7   | G    | WORKER      | 4    |
| 8   | H    | BOSS        | 8    |
+-----+------+-------------+------+
8 rows in set (0.00 sec)

An employee can't be the BOSS of themselves. Hence

mysql> INSERT INTO Employee VALUES ("8", "H", "BOSS",   "8");  
Query OK, 1 row affected (0.04 sec)

should be declined by some constraint. How can one add such a constraint?

If possible suggest without using a trigger.

score 2 · Accepted Answer · edited Apr 13 '17 at 12:42

This would be easily solved with a check constraint but these are not yet implemented in MySQL (they are just parsed for "compatibility reasons" during the CREATE TABLE definition and completely ignored after that.)

So, solution 1 is rather obvious. Move to a DBMS that has CHECK constraint implemented, like SQL-Server, Oracle, Postgres, etc. (even MS-Access has them!):

ALTER TABLE Employee
  ADD CONSTRAINT Employee_cannot_be_Boss_of_himself
    CHECK (mssn <> ssn) ;

Solution 2 is to drop the designation column altogether. From your description, all 'Owners' have null mssn and all others have their "manager's" ssn stored in the mssn column.

After dropping that column, you can always calculate it in a view:

CREATE VIEW EmployeeDesignated AS
  SELECT ssn
       , name
       , CASE WHEN (mssn IS NULL OR mssn = ssn) 
                THEN 'OWNER'
              WHEN EXISTS (SELECT * FROM Employee ew WHERE ew.mssn = e.ssn)  
                THEN 'BOSS'
              ELSE 'WORKER'
         END AS designation
       , mssn
  FROM Employee AS e ;

This just enforces that when a new employee is inserted, if the mssn is same as the ssn, the employee is assigned as 'Owner'.

You may have performance issues though, depending on how you use the designation column. Views inside views or complex queries are not the best deal for MySQL's optimizer.

Solution 3 is to emulate the CHECK constraint using the method described in this answer: Check constraint does not work?

This requires that you upgrade to either MySQL version 5.7 or to MariaDB 5.5.

Solution 4 is to wait for them to be implemented. There are rumors (it's 2016 now) that they will be in the next version.

Restriction on self referencing on insert in MYSQL?

1 Answers1

Linked