Defining a parent key and unique index

A parent key is either a primary key or a unique key in the parent table of a referential constraint. The values of a parent key determine the valid values of the foreign key in the constraint. You must create a unique index on a parent key.

About this task

The primary key of a table, if one exists, uniquely identifies each occurrence of an entity in the table. The PRIMARY KEY clause of the CREATE TABLE or ALTER TABLE statements identifies the column or columns of the primary key. Each identified column must be defined as NOT NULL.

Another way to allow only unique values in a column is to specify the UNIQUE clause when you create or alter a table.

A table that is to be a parent of dependent tables must have a primary or a unique key; the foreign keys of the dependent tables refer to the primary or unique key. Otherwise, a primary key is optional. Consider defining a primary key if each row of your table does pertain to a unique occurrence of some entity. If you define a primary key, an index must be created (the primary index) on the same set of columns, in the same order as those columns. If you are defining referential constraints for Db2 to enforce, takes steps to maintain data integrity read before creating or altering any of the tables involved.

A table can have no more than one primary key. A primary key has the same restrictions as index keys:

  • The key can include no more than 64 columns.
  • You cannot specify a column name twice.
  • The sum of the column length attributes cannot be greater than 2000.

You define a list of columns as the primary key of a table with the PRIMARY KEY clause in the CREATE TABLE statement.

Procedure

To add a primary key to an existing table:

Use the PRIMARY KEY clause in an ALTER TABLE statement. In this case, a unique index must already exist.
Consider the following items when you plan for primary keys:
  • The theoretical model of a relational database suggests that every table should have a primary key to uniquely identify the entities it describes. However, you must weigh that model against the potential cost of index maintenance overhead. Db2 does not require you to define a primary key for tables with no dependents.
  • Choose a primary key whose values will not change over time. Choosing a primary key with persistent values enforces the good practice of having unique identifiers that remain the same for the lifetime of the entity occurrence.
  • A primary key column should not have default values unless the primary key is a single TIMESTAMP column.
  • Choose the minimum number of columns to ensure uniqueness of the primary key.
  • A view that can be updated that is defined on a table with a primary key should include all columns of the key. Although this is necessary only if the view is used for inserts, the unique identification of rows can be useful if the view is used for updates, deletes, or selects.
  • Drop a primary key later if you change your database or application using SQL.