UNIQUE and PRIMARY KEY constraints can only be defined on a column set that is also constrained to be NOT NULL.
To create a composite, or multicolumn, primary key for a table, you must specify the PRIMARY KEY constraint at the table level, not the column level.
Vantage also supports the related constraints UNIQUE INDEX and UNIQUE PRIMARY INDEX.
UNIQUE Constraints
UNIQUE constraints specify that their column set must contain unique values. Vantage implements UNIQUE constraints as either a unique secondary indexes or as a single-table join index.
- UNIQUE constraints must be paired with a NOT NULL attribute specification.
- UNIQUE constraints can be defined at column-level (simple) or at table-level (composite).
- Column-level UNIQUE constraints refer only to the column on which specified.
- Table-level UNIQUE constraints can be defined on multiple columns using a column name list.
- A table-level UNIQUE constraint can be defined on a maximum of 64 columns.
- A maximum of 100 table-level constraints can be defined for any table.
- UNIQUE constraints cannot be defined on a global temporary trace table.
- You can define a UNIQUE constraint for a column-partitioned table.
You cannot define a UNIQUE constraint for a nonpartitioned NoPI table.
Otherwise, the system does not return an error, but instead converts the UNIQUE constraint specification to a UNIQUE NOT NULL secondary index specification. As a result of this conversion, if you submit a SHOW TABLE statement on such a table, the create text that the system returns does not show a UNIQUE constraint specification, but instead returns a UNIQUE NOT NULL secondary index or single-table join index specification on the column set that had been specified for the UNIQUE constraint.
This is different from the ordinary implementation of a UNIQUE constraint column set as a UNIQUE NOT NULL secondary index because Vantage changes the stored create text for a NoPI table defined with a UNIQUE constraint, it does not simply implement the constraint as a USI.
- UNIQUE constraints cannot be defined on columns defined with any of the following data types.
- BLOB
- CLOB
- Period
- Geospatial
PRIMARY Constraints
The primary key of a table is a column set that uniquely identifies each row of that table. See Semantic Constraint Specifications . You cannot define more than one primary key for a table. Primary keys are a logical construct in the relational model, typically implemented physically as the unique primary index.
To create a composite, or multicolumn, primary key for a table, you must specify the PRIMARY KEY constraint at the table level, not the column level.
- BLOB
- CLOB
- Period
- Geospatial
When a table has a nonunique primary index, consider defining its primary key explicitly using the PRIMARY KEY clause. Primary and other alternate keys are also used with foreign keys to enforce referential integrity relationships between tables (see Standard Referential Integrity Constraints, Batch Referential Integrity Constraints, and Referential Constraints).