3

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.

10
  • 2
    You could with CHECK (mssn <> ssn) but check constraints are not implemented in MySQL. Nov 23 '12 at 12:36
  • In short, you can't, without major reconstruction of the table. Nov 23 '12 at 12:36
  • @ypercube : Thanks to answering me again! ... How to reconstruct? Please explain bit more. Nov 23 '12 at 12:39
  • .... or upgrade to a DBMS that does support check constraints (so pretty much every other DBMS) Nov 23 '12 at 12:58
  • 1
    Pretty much, yes. Nov 23 '12 at 13:24
2

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.

1
  • I learn something new from your answer. Thanks Lots ypercube. Nov 24 '12 at 5:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.