Adding foreign keys - IBM DB2 9.7 for Linux, UNIX, and Windows

You can use the Control Center or the ALTER TABLE statement to add foreign keys.

About this task

When a foreign key is added to a table, packages and cached dynamic SQL containing the following statements might be marked as invalid:

Statements that insert or update the table containing the foreign key
Statements that update or delete the parent table

Procedure

To add foreign keys using the Control Center:
1. Expand the object tree until you see the Tables folder.
2. Right-click the table you want to modify, and select Alter from the pop-up menu.
3. On the Keys page, click Add. On the Add Foreign Keys window, specify the parent table information.
4. Select one or more columns to be foreign keys.
5. Specify what action is to take place on the dependent table when a row of the parent table is deleted or updated. You can also add a constraint name for the foreign key.

To add foreign keys using the command line, enter:

ALTER TABLE name
   ADD CONSTRAINT column_name
    FOREIGN KEY column_name
    ON DELETE action_type
    ON UPDATE action_type

Example

The following examples show the ALTER TABLE statement to add primary keys and foreign keys to a table:

   ALTER TABLE PROJECT
     ADD CONSTRAINT PROJECT_KEY
         PRIMARY KEY (PROJNO)
   ALTER TABLE EMP_ACT
     ADD CONSTRAINT ACTIVITY_KEY
         PRIMARY KEY (EMPNO, PROJNO, ACTNO)
     ADD CONSTRAINT ACT_EMP_REF
         FOREIGN KEY (EMPNO)
         REFERENCES EMPLOYEE
         ON DELETE  RESTRICT
     ADD CONSTRAINT ACT_PROJ_REF
         FOREIGN KEY (PROJNO)
         REFERENCES PROJECT
         ON DELETE CASCADE