Defining a foreign key

Use foreign keys to enforce referential relationships between tables. A foreign key is a column or set of columns that references the parent key in the parent table.

To define a foreign key, use one of the following approaches:

• Issue a CREATE TABLE statement and specify a FOREIGN KEY clause.
  1. Choose a constraint name for the relationship that is defined by a foreign key.
     If you do not choose a name, Db2 generates one from the name of the first column of the foreign key, in the same way that it generates the name of an implicitly created table space.
     For example, the names of the relationships in which the employee-to-project activity table is a dependent would, by default, be recorded (in column RELNAME of SYSIBM.SYSFOREIGNKEYS) as EMPNO and PROJNO.

     The name is used in error messages, queries to the catalog, and DROP FOREIGN KEY statements. Hence, you might want to choose one if you are experimenting with your database design and have more than one foreign key that begins with the same column (otherwise Db2 generates the name).

  2. Specify column names that identify the columns of the parent key.
     A foreign key can refer to either a unique or a primary key of the parent table. If the foreign key refers to a non-primary unique key, you must specify the column names of the key explicitly. If the column names of the key are not specified explicitly, the default is to refer to the column names of the primary key of the parent table.
• Issue an ALTER TABLE statement and specify the FOREIGN KEY clause.
  You can add a foreign key to an existing table; in fact, that is sometimes the only way to proceed. To make a table self-referencing, you must add a foreign key after creating it. When a foreign key is added to a populated table, the table space is put into CHECK-pending status.

CREATE TABLE DSN8C10.EMPPROJACT
      (EMPNO     CHAR(6)          NOT NULL,
       PROJNO    CHAR(6)          NOT NULL,
       ACTNO     SMALLINT         NOT NULL,
       CONSTRAINT REPAPA FOREIGN KEY (PROJNO, ACTNO)
               REFERENCES DSN8C10.PROJACT ON DELETE RESTRICT,
       CONSTRAINT REPAE FOREIGN KEY (EMPNO)
               REFERENCES DSN8C10.EMP ON DELETE RESTRICT)
  IN DATABASE DSN8D12A;