5.2.9 Specifying a primary key (uniqueness constraint definition) (PRIMARY KEY) [Single-chunk table]
When defining a table as a single-chunk table, consider whether to define a primary key. You cannot define a primary key if you define the table as a multi-chunk table.
A key for uniquely identifying a row within a base table is called a primary key. By defining a primary key, you can maintain the uniqueness of the base table's data.
To define a primary key, specify a uniqueness constraint definition in the CREATE TABLE statement.
When a primary key is defined, the following two constraints are applied to the columns that comprise the primary key:
-
Uniqueness constraint
This constraint does not permit a column value (or column values in multiple columns) to be duplicated.
-
NOT NULL constraint
This constraint (NOT NULL) does not permit a column value to be the null value.
A unique index is automatically defined, with the columns holding the primary key set as the indexed columns. The name of the index identifier of the unique index is the same as the constraint name in the uniqueness constraint definition specified in the CREATE TABLE statement. For details about unique indexes, see 5.3.5 Points to consider in determining the columns to be defined for a unique index.
When you define a primary key, select a primary key from a column or combination of columns (candidate keys) that can uniquely identify a row within the base table. If there are multiple candidate keys, define the most significant candidate key as the primary key.
- Important
-
Even when a primary key is defined for the base table, the uniqueness constraint cannot be guaranteed if data containing duplicate values is imported using the adbimport command. In such a case, search the base table for duplicate keys and delete the rows that have the searched key value. For details about how to search for duplicate keys, see (2) Steps to take when the uniqueness constraint is violated in 15.9.2 Steps to take when the uniqueness constraint is violated (when the KFAA61205-W message is output).