---

Add/delete a constraint to/from a relation

---

• Adding a new constraint.
  • The alter table ... ADD CONSTRAINT command is used to add a new constraint to a relation:

    ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;

  • Example:

    ALTER TABLE employee ADD CONSTRAINT EmpPrimKey PRIMARY KEY(ssn);

    ---

    ---

  • Note:

    Before the new constraint is added, the validity is verified: If an existing tuple in the relation violates the new constraint: The new constraint will not be added !!!

  ---

  ---

  ---

  ---

• Removing an existing constraint.
  • The alter table ... DROP CONSTRAINT command is used to delete a constraint from a relation:

    ALTER TABLE table_name DROP CONSTRAINT constraint_name ;

  • Example:

    ALTER TABLE employee DROP CONSTRAINT EmpPrimKey;

    ---

  • Important note:

    You can only drop a named constraint (= a constraint that has was given a name when the constraint was defined) !!! An anonymous (= unnamed)constraint can only be removed by dropping the table ....

    ---

    ---

    ---

  • MySQL's syntax for dropping a referential constraint:

    ALTER TABLE table_name DROP FOREIGN KEY constraint_name ;

   

   

  ---

  ---

  ---

  ---

  ---

  "Circular" constraints...

  ---

  ---

• "Circular" integrity constraints.
  • Circular referential integrity constraints :

    Multiple referential integrity constraints can result in a cycle of dependencies

  • Example: in the company database schema

    Consider the cycle created by the following referential integrity constraints:

    employee(dno) → department(dnumber) department(mgrssn) → employee(ssn)

  • $64,000 Question:

    How can we define these circular referential integrity constraints ???

    ---

  • Attempt #1

    CREATE TABLE emp1 ( ssn CHAR(9), dno INTEGER, CONSTRAINT empPrimaryKey PRIMARY KEY (ssn), CONSTRAINT empForeignKey1 FOREIGN KEY (dno) REFERENCES dept1(dnumber) ); CREATE TABLE dept1 ( dnumber INTEGER, mgrssn CHAR(9), CONSTRAINT deptPrimaryKey PRIMARY KEY (dnumber), CONSTRAINT deptForeignKey1 FOREIGN KEY (mgrssn) REFERENCES emp1(ssn) );

    Result:

    SQL> CREATE TABLE emp1 ( ssn CHAR(9), dno INTEGER, CONSTRAINT empPrimaryKey PRIMARY KEY (ssn), CONSTRAINT empForeignKey1 FOREIGN KEY (dno) REFERENCES dept1(dnumber) ) ERROR 1005 (HY000): Can't create table 'companyDB.emp1' (errno: 150) (My comment: this error message SUCKS. No reason given ??? I guess you get what yoy pay for. Oracle's error message is much clearer...)

  • Reason:

    You cannot define a referential integrity constraint that references a non-existing attribute : When we define the relation emp1 , the attribute dnumber in relation dept1 does not exist !!!

    ---

    ---

  • Attempt #2: (solution)

    First, create the relations without any referential constraints Then add the referential constraints later using the ALTER TABLE ... ADD CONSTRAINT command

    Example:

    CREATE TABLE emp1 ( ssn CHAR(9), dno INTEGER, CONSTRAINT empPrimaryKey PRIMARY KEY (ssn) ); CREATE TABLE dept1 ( dnumber INTEGER, mgrssn CHAR(9), CONSTRAINT deptPrimaryKey PRIMARY KEY (dnumber) ); ALTER TABLE emp1 ADD CONSTRAINT empForeignKey1 FOREIGN KEY (dno) REFERENCES dept1(dnumber); ALTER TABLE dept1 ADD CONSTRAINT deptForeignKey1 FOREIGN KEY (mgrssn) REFERENCES emp1(ssn);

  ---

  ---

  ---

  ---

  ---

• Inserting tuples when we have "circular" referential integrities
  • Tuple insertion with circular referential integrities:

    Data insertion is very complicated when we have circular referential integrities....

  • Example:

    INSERT INTO emp1 VALUES ('444444444', 12); INSERT INTO dept1 VALUES (12, '444444444');

    Result:

    SQL> INSERT INTO emp1 VALUES ('444444444', 12); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`companyDB`.`emp1`, CONSTRAINT `empForeignKey1` FOREIGN KEY (`dno`) REFERENCES `dept1` (`dnumber`))

    Reason:

    When you insert the tuple ('444444444', 12) in the relation emp1 , the referenced tuple (12, '444444444') does not exist in relation dept1 !!!

    ---

    ---

    ---

  • Textbook Solution: (according to the SQL standard)

    use deferred constraints

    Deferred constraint:

    Deferred constraint = a constraint that is checked (verified) when the user issues the COMMIT command

    ---

    ---

  • Example: (this works in Oracle, but not in MySQL)

    ALTER TABLE emp1 DROP CONSTRAINT empForeignKey1; ALTER TABLE emp1 ADD CONSTRAINT empForeignKey1 FOREIGN KEY (dno) REFERENCES dept1(dnumber) INITIALLY DEFERRED DEFERRABLE ; INSERT INTO emp1 VALUES ('444444444', 12); INSERT INTO dept1 VALUES (12, '444444444'); COMMIT; // Check constraints NOW !

    ---

    MySQL implementation issue:

    In MySQL, all contraints are enforced immediately I.e.: MySQL does not support deferred constraints          Therefore: You cannot have circular referential integrity constraints in MySQL....

  ---

  ---

  ---

  ---

  ---

• Postscript: deleting tables when there is a circular referential integrity constraint
  • Another problem when you have a circular referential integrity constraint:

    You cannot drop a table that is involved in the circular referential integrity constraint....

    ---

  • Solution:

    Break the cycle by drop one of the referential integrity constraint !!! Example: ALTER TABLE emp1 DROP CONSTRAINT empForeignKey1; MySQL syntax: ALTER TABLE emp1 DROP FOREIGN KEY empForeignKey1;

    ---

  • Now you can drop the tables:

    drop table dept1; drop table emp1;

    (in that order !) -- because there is a foreign key constraint in dept1...