Adding referential constraints to existing tables

You can use the ALTER TABLE statement to add referential constraints to existing tables.

Before you begin

Before you load tables that are involved in a referential constraint or check constraint, you need to create exception tables. An exception table contains the rows that the CHECK DATA utility identified because they violate referential constraints or check constraints.

About this task

Assume that the tables in the sample application (the Db2 sample activity table, project table, project activity table, employee table, and department table) already exist, have the appropriate column definitions, and are already populated.

Now, suppose that you want to define relationships among the sample tables by adding primary and foreign keys with the ALTER TABLE statement. The following rules apply to these relationships:

An existing table must have a unique index on its primary key columns before you can add the primary key. The index becomes the primary index.
You must add the parent key of the parent table before adding the corresponding foreign key of the dependent table.

You can build the same referential structure in several different ways; however, the following process might be the simplest to understand.

Procedure

To add a referential constraint to an existing table:

Create a unique index on the primary key columns for any table that does not already have one.
For each table, issue the ALTER TABLE statement to add its primary key.

In the next steps, you issue the ALTER TABLE statement to add foreign keys for each table, except for the activity table. The table space remains in CHECK-pending status, which you can reset by running the CHECK DATA utility with the DELETE(YES) option.

Deletions by the CHECK DATA utility are not bound by delete rules. The deletions cascade to all descendents of a deleted row, which can be disastrous. For example, if you delete the row for department (A00) from the department table, the deletion might propagate through most of the referential structure. The remaining steps prevent deletion from more than one table at a time.
Add the foreign keys for the department table and run CHECK DATA DELETE(YES) on its table space.
Then, correct any rows in the exception table, and use INSERT to replace the rows in the department table. This table is now consistent with existing data.
Drop the foreign key on MGRNO in the department table.
This step drops the association of the department table with the employee table, without changing the data of either table.
Add the foreign key to the employee table, run the CHECK DATA utility again, and correct any errors.
If errors are reported, be particularly careful not to make any row inconsistent with the department table when you make corrections.
Add the foreign key on MGRNO to the department table, which again leaves the table space in CHECK-pending status. Then, run the CHECK DATA utility.
If you have not changed the data since the previous check, you can use the DELETE(YES) option, and the deletions will not cascade.
For each of the following tables, in the order shown, add its foreign keys, run the CHECK DATA utility with the DELETE(YES) option, and correct any rows that are in error:
1. Project table
2. Project activity table
3. Employee to project activity table

Examples

Begin general-use programming interface information.

Create the DEPT table and define its primary key on the DEPTNO column. The PRIMARY KEY clause of the CREATE TABLE statement defines the primary key.
```
CREATE TABLE DEPT
  ⋮
  PRIMARY KEY (DEPTNO);         
```
Create the EMP table and define its primary key as EMPNO and its foreign key as DEPT. The FOREIGN KEY clause of the CREATE TABLE statement defines the foreign key.
```
CREATE TABLE EMP
  ⋮
  PRIMARY KEY (EMPNO)           
  FOREIGN KEY (DEPT)
    REFERENCES DEPT (DEPTNO)
    ON DELETE SET NULL;
```

Alter the DEPT table to add the definition of its foreign key, MGRNO.

ALTER TABLE DEPT
  FOREIGN KEY (MGRNO)
    REFERENCES EMP (EMPNO)
    ON DELETE RESTRICT;

End general-use programming interface information.