Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 BOSS of himself. 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 to add such constraint?
If possible suggest without trigger.

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

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
I learn something new from your answer. Thanks Lots ypercube. –  Grijesh Chauhan Nov 24 '12 at 5:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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