PRIMARY KEY constraints specify the primary key column set in a table definition. Vantage uses primary keys to enforce both row uniqueness and referential integrity.
Whether a PRIMARY KEY constraint is treated as a column-level constraint or a table-level constraint depends on whether the primary key is simple or composite.
- Only one primary key can be defined per table.
- The following table explains the column limits for column-level and table-level primary key constraints.
| PRIMARY KEY Constraint | Definition Level |
|---|---|
| Simple (defined on a single column) | Column. You can define a simple PRIMARY KEY constraint at table-level, but there is no reason to do so. |
| Composite (defined on multiple columns) | Table. Defining a table-level PRIMARY KEY constraint is the only way you can create a multicolumn primary key. |
- Defining a primary key for a table is never required, but is recommended for documentation purposes as part of a policy of enforcing data integrity in those cases where the logical primary key is not chosen to be the unique primary index.
- A primary key can be defined on a maximum of 64 columns.
- A PRIMARY KEY constraint cannot be defined on the same column set as the set used to define the nonunique primary index for a table.
When the primary key is defined on a single column, PRIMARY KEY constraints are treated as column-level constraints.
When the primary key is defined on multiple columns, PRIMARY KEY constraints are treated as table-level constraints.
- A primary key constraint can be defined on the same columns as a unique secondary index or unique primary index. A primary key is implemented as follows:
- If the table is defined explicitly with a primary index or unique primary index on different columns than the primary key columns or if the table is defined as NO PRIMARY INDEX or PRIMARY AMP INDEX, then the primary key is implemented as a unique secondary index.
- If a nonpartitioned, nontemporal table does not have an explicit primary index, explicit primary AMP index, or explicit NoPI and no USI or UNIQUE constraint is specified on the same columns as the primary key, then the default is a unique primary index on the same columns as the primary key constraint.
In physical database design, candidate keys, whether chosen to be a primary key or not, are defined internally as either a UNIQUE NOT NULL secondary index or as a single-table join index.
- You cannot define a PRIMARY KEY on a row-level security-protected column.