You can define the primary index for a table using either a PRIMARY KEY or a UNIQUE constraints as the default primary index in a CREATE TABLE statement.
The following bullets list the rules for defining primary keys and UNIQUE constraints with respect to primary indexes:
- A table can have at most 1 primary key and need not have a primary index.
- If a table has a primary index, it can have only 1.
- You cannot define a primary index and a PRIMARY KEY or UNIQUE constraint on the same column set.
You can still define a relationship for referential integrity by referencing the UPI of a table even if no primary key is defined explicitly for that table because it is always valid to define a referential integrity relationship with any alternate key.
- If both a primary index and primary key are specified in a CREATE TABLE statement, then the primary index is the hashing index and the primary key is mapped to a unique secondary index by default.
- If a primary key is specified in a CREATE TABLE statement, but a primary index is not, then the system maps the primary key to a UPI by default.
- If neither primary index nor primary key is specified in a CREATE TABLE statement, then the system defines the first column that has a UNIQUE constraint as the UPI by default.
- If there is no PRIMARY INDEX, PRIMARY KEY, or UNIQUE constraint defined in a CREATE TABLE statement, and the PrimaryIndexDefault parameter is set to either D or P, then Vantage defines the first index-eligible column defined for the table to be its primary index.
The system defines this index as a NUPI by default except for the case of a single column table defined with the SET (no duplicate rows permitted) option, in which case the system defines it as a UPI.
- If there is no PRIMARY INDEX, PRIMARY KEY constraint, or UNIQUE constraint defined in a CREATE TABLE statement, and the PrimaryIndexDefault parameter is set to N, then Vantage creates the table with no primary index.
- Columns defined with either of the following constraints cannot be defined to be nullable.
- PRIMARY KEY
- Columns defined with any of the following constraints can be defined as nullable:
- PRIMARY INDEX
- UNIQUE PRIMARY INDEX
- UNIQUE INDEX
You should declare the column set that constitutes these index types to be NOT NULL unless there is a compelling reason not to.
- You cannot define a PRIMARY KEY or UNIQUE constraint with the same column set as a secondary index defined on the same table.
- You cannot define a UNIQUE constraint explicitly on the same columns as a PRIMARY KEY constraint.