Enforcing Referential Integrity with Existing Unenforced Foreign Keys

You can enforce referential integrity with unenforced foreign keys.

1. Identify the candidate key to foreign key relationship.
   In the schema in the Foreign Key Example, there are two such relationships:

   • Foreign key(EMPLOYEE.DeptNo to Candidate key(DEPT.DeptNo)

   • Foreign key(DEPT.Mgr) to Candidate key (EMPLOYEE.EMPNo)

2. Add a primary key or unique constraint on the candidate key via the ALTER TABLE statement if none exist. (In the preceding example, the primary key already exists.) All candidate key values must be unique and non-null.
3. Drop the unenforced foreign key constraint via the ALTER TABLE statement if one exists.
   For example:

   ALTER TABLE DEPT DROP FOREIGN KEY MGR_EMPNO;
   ALTER TABLE EMPLOYEE DROP FOREIGN KEY DEPT;

   In the preceding schema, we need to drop unenforced foreign key constraints MGR_EMPNO and EMPLOYEE(DeptNo) referencing DEPT(DeptNo). If there is no user specified role name for EMPLOYEE(DeptNo) to DEPT(DeptNo), the default role name is the same as the primary table, in other words, DEPT.

4. Add the foreign key constraint(s). For example:

   ALTER TABLE DEPT ADD FOREIGN KEY MGR_EMPNO(Mgr) REFERENCES EMPLOYEE(EmpNo);
   ALTER TABLE EMPLOYEE ADD FOREIGN KEY EMP_DEPT(DeptNo) REFERENCES DEPT(DeptNo);