- CURRENT VALIDTIME PRIMARY KEY and CURRENT VALIDTIME UNIQUE constraints ensure that the value for the constrained column in a row is unique for all instances of time from current time through the future. Current and future rows that have overlapping valid-time periods are prevented from having the same value in the constrained columns.
- SEQUENCED VALIDTIME PRIMARY KEY and SEQUENCED VALIDTIME UNIQUE constraints ensure that the value for the constrained column in a row is unique for all instances of time, including history, current, and future. Any rows that have overlapping valid-time periods are prevented from having the same value in the constrained columns.
- NONSEQUENCED VALIDTIME PRIMARY KEY and NONSEQUENCED VALIDTIME UNIQUE constraints treat the valid-time column as a nontemporal column. These constraints ensure that the value for the constrained column in a row is unique amongst all rows in the table. All open rows are prevented from having the same value in the constrained columns. Nonsequenced PK/unique constraints are identical to PK/unique constraints on nontemporal tables. These types of constraints are rarely useful on temporal tables, and are not recommended. Due to the near duplication of rows that happens automatically as rows are modified in temporal tables, a nonsequenced PK/unique constraint would be violated very quickly. The same situation is true for USIs applied to temporal tables, which are also not recommended.
Example: Primary key and unique constraints
Assume that the TEMPORAL_DATE is November 2, 2006 (2006/11/02) and a valid-time table is defined with columns: Col1, Col2, and VTCol where VTCol is a valid-time column. Assume further that a CURRENT VALIDTIME UNIQUE constraint is defined on Col2. The following row:
| Col1 | Col2 (unique) | VTCol |
|---|---|---|
| 5 | 24 | ('2006/10/20', '2007/10/20') |
does not violate the constraint with the row:
| 6 | 24 | ('2008/01/20', '9999/12/31') |
because the valid-time periods do not overlap. The same first row would violate the current unique constraint with the row:
| 7 | 24 | ('2007/09/20', '9999/12/31') |
because the time periods overlap from 2007/09/20 to 2007/10/20.
If the table also had a transaction-time as the fourth column, the following row:
| Col1 | Col2 | VTCol | TTCol |
|---|---|---|---|
| 8 | 24 | ('2008/01/20', '9999/12/31') | ('2006/09/20', '2006/09/25') |
would not violate the current constraint because the row is closed in transaction time (has an end date prior to UNTIL_CLOSED), so this row is not considered for constraint checking.
Indexes for Primary Key and Unique Constraints
Because of the way rows are duplicated as a result of modifications to temporal tables, most primary key and unique constraints defined on temporal tables are implemented by means of system-defined join indexes (SJIs). These indexes enforce the uniqueness on an appropriate subset of rows, according to whether the constraint is current, sequenced, or nonsequenced.
Example: System-defined join index
A current unique constraint on a bitemporal table causes an SJI to be created and maintained automatically from selected columns of the temporal table. The primary index of the SJI is the constrained column or columns of the temporal table. The valid-time and transaction-time columns are selected using an appropriately qualified WHERE clause that limits the rows in the index to current and future rows:
CREATE JOIN INDEX tablename_TJI number AS SELECT ROWID, ConstrainedColumn, VTColumn, TTcolumnFROM tablenameWHERE END(VTColumn) ≥ CURRENT_DATE - INTERVAL '2' DAY AND END(TTcolumn) IS UNTIL_CLOSED PRIMARY INDEX (ConstrainedColumn);
INTERVAL ‘2’ DAY is required because current rows could be inserted in a time zone that is up to two days prior to the date for the time zone in which the index is created.
SJIs are created in the same database as the constrained temporal table. They are named automatically by the system using a naming convention of tablename_TJI number, where tablename is the first 121 characters of the name of the temporal table for which a PK or unique constraint has been defined, and number is the index ID of the constraint, a unique number that identifies the SJI. Consequently, temporal tables should be named such that the first 121 characters provides per-table uniqueness in the name.
SJIs use the same map for data distribution as the constrained temporal table. If the temporal table uses a sparse map, the SJI also shares the same colocation name as the constrained table. For more information on contiguous and sparse maps, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ - Database Design, B035-1094.
The PK or unique constraint is not allowed if the associated SJI would cause the maximum permitted number of secondary indexes to be exceeded for the table.
As time passes, the values of CURRENT_DATE and CURRENT_TIMESTAMP differ from the values that were used when the SJI was created. Because of this, current and future rows in SJIs, over time, become history rows, and therefore no longer needed in the index to enforce the current constraint.
Use the ALTER TABLE TO CURRENT statement periodically to update SJIs and PPIs created for temporal tables. ALTER TABLE TO CURRENT transitions history rows out the SJIs created for current primary key and unique constraints. For more information on ALTER TABLE TO CURRENT, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
Because nonsequenced constraints treat temporal columns as if they were nontemporal, a nonsequenced valid-time PK or unique constraint on a valid-time table is implemented automatically by making the constrained column a USI. For bitemporal tables, a PK or unique constraint must be limited to rows that are open in transaction time, so an SJI is used. The SJI uses a WHERE clause to select only the open rows from the transaction-time column.