Adding parent keys and foreign keys

You can add primary parent keys, unique parent keys, and foreign keys to an existing table.

About this task

Introductory concepts

When you add parent keys and foreign keys to an existing table, you must consider certain restrictions and implications.

  • If you add a primary key, the table must already have a unique index on the key columns. If multiple unique indexes include the primary key columns, the index that was most recently created on the key columns becomes the primary index. Because of the unique index, no duplicate values of the key exist in the table; therefore you do not need to check the validity of the data.
  • If you add a unique key, the table must already have a unique index with a key that is identical to the unique key. If multiple unique indexes include the primary key columns, Db2 arbitrarily chooses a unique index on the key columns to enforce the unique key. Because of the unique index, no duplicate values of the key exist in the table; therefore you do not need to check the validity of the data.
  • You can use only one FOREIGN KEY clause in each ALTER TABLE statement; if you want to add two foreign keys to a table, you must execute two ALTER TABLE statements.
  • If you add a foreign key, the parent key and unique index of the parent table must already exist. Adding the foreign key requires the ALTER privilege on the dependent table and either the ALTER or REFERENCES privilege on the parent table.
  • Adding a foreign key establishes a referential constraint relationship. Db2 does not validate the data when you add the foreign key. Instead, if the table is populated (or, in the case of a nonsegmented table space, if the table space has ever been populated), the table space that contains the table is placed in CHECK-pending status, just as if it had been loaded with ENFORCE NO. In this case, you need to execute the CHECK DATA utility to clear the CHECK-pending status.
  • You can add a foreign key with the NOT ENFORCED option to create an informational referential constraint. This action does not leave the table space in CHECK-pending status, and you do not need to execute CHECK DATA.

Procedure

To add a key to a table:

  1. Choose the type of key that you want to add.
  2. Begin general-use programming interface information.Add the key by using the ALTER TABLE statement.
    Option Description
    Adding a primary key To add a primary key to an existing table, use the PRIMARY KEY clause in an ALTER TABLE statement. For example, if the department table and its index XDEPT1 already exist, create its primary key by issuing the following statement: 
    ALTER TABLE DSN8910.DEPT
   ADD PRIMARY KEY (DEPTNO);
    Adding a unique key To add a unique key to an existing table, use the UNIQUE clause of the ALTER TABLE statement. For example, if the department table has a unique index defined on column DEPTNAME, you can add a unique key constraint, KEY_DEPTNAME, consisting of column DEPTNAME by issuing the following statement: 
    ALTER TABLE DSN8910.DEPT
   ADD CONSTRAINT KEY_DEPTNAME UNIQUE 
  (DEPTNAME);
    Adding a foreign key To add a foreign key to an existing table, use the FOREIGN KEY clause of the ALTER TABLE statement. The parent key must exist in the parent table before you add the foreign key. For example, if the department table has a primary key defined on the DEPTNO column, you can add a referential constraint, REFKEY_DEPTNO, on the DEPTNO column of the project table by issuing the following statement: 
    ALTER TABLE DSN8910.PROJ
   ADD CONSTRAINT REFKEY_DEPTNO FOREIGN 
   KEY (DEPTNO) REFERENCES DSN8910.DEPT 
   ON DELETE RESTRICT;

    End general-use programming interface information.