Defining constraints on object identifier columns

This topic describes how to define constraints on object identifier columns.

About this task

If you want to use the object identifier column as a key column of the parent table in a foreign key, you must first alter the typed table to add an explicit unique or primary key constraint on the object identifier column. For example, assume that you want to create a self-referencing relationship on employees in which the manager of each employee must always exist as an employee in the employee table, as shown in Figure 1.

To define constraints on an object identifier column to create a self-referencing relationship on an object:

1. Create the type, for example:

      CREATE TYPE Empl_t AS
         (Name VARCHAR(10), Mgr REF(Empl_t))
         MODE DB2SQL;

2. Create the typed table, for example:

      CREATE TABLE Empl OF Empl_t
         (REF IS Oid USER GENERATED);

3. Add the primary or unique constraint on the Oid column, for example:

      ALTER TABLE Empl ADD CONSTRAINT pk1 UNIQUE(Oid);

4. Add the foreign key constraint, for example:

      ALTER TABLE Empl ADD CONSTRAINT fk1 FOREIGN KEY(Mgr)
         REFERENCES Empl (Oid);