Adding foreign keys

These steps are part of the larger tasks of creating a table and altering a table. When you complete the steps for adding foreign keys, return to either creating a table or altering a table.

To add foreign keys:

1. In the Add Foreign Key window, use the Table owner box to specify the user identifier that owns the parent table.
2. Use the Table name box to specify the name of the parent table. It must have a unique or primary key, exist at the current subsystem, and must not be a catalog table.
3. In the Unique indexes box, select the name of the key (primary or unique) in the parent table which you want to reference for this foreign key. The Index columns box displays a list of the columns defined for the selected key.
4. In the Available columns box, select a column or columns that you want to define as a foreign key. The foreign key must have the same number of columns as the parent key. Except for their names, default values, null attributes, and check constraints, each column that you select must match a column for the parent key. If a column of the foreign key has a field procedure, the corresponding column of the parent key must have the same field procedure and an identical field description. If the foreign key includes a distinct type column, the corresponding column of the nominated parent key must be the same distinct type.

   The selected columns cannot be LOB or ROWID columns.

   You can specify up to 64 columns, and the sum of their lengths must not exceed 254 minus the number of nullable columns.

   The foreign key is a duplicate if the foreign key and parent table are the same as the foreign key and parent table of a foreign key previously defined for this table. A duplicate foreign key is ignored with a warning.

5. Click on the > push button to move the selected column or columns to the Foreign key columns box. The order of the foreign key columns must match the order of the parent key columns.
6. Optional: In the On delete box, specify what action is to take place on the dependent table when a row is deleted from the parent table.

   If you are altering an existing table to add a foreign key that is referencing the table itself, you must specify CASCADE.

7. Optional: In the Constraint name box, type a name for the foreign key. If you specify a name, it must be different from the names of any referential or check constraints previously specified on the table. If you do not specify a name, a name is generated for you.
8. Click Apply to add the new foreign key.
9. Repeat steps 1 through 8 to add another foreign key to your table.
10. Click Close to close the Add Foreign Key window.

If you altered an existing table to add this foreign key, and the table is populated (or in the case of a nonsegmented table space, if the table space has ever been populated), the table space containing the table is placed in check pending status. To clear the check pending status, you need to check the data on the table space. For more information, see checking data in table spaces.

Depending on the task that you were doing when you added this foreign key, return to either creating a table or altering a table.