Purpose
Valid-time table definitions can include primary key and unique constraints that prevent rows from having valid-time periods that overlap. A system-defined join index is created automatically for constraint checking during modifications to the table.
Syntax
{ PRIMARY KEY | UNIQUE } (
column_list [, valid_time_period WITHOUT OVERLAPS ]
)
- column_list
- The column name or the comma-separated list of columns that serve as the primary key or uniqueness constraint for the table.
- valid_time_period
- The name of the valid-time derived period column.
- WITHOUT OVERLAPS
- Specifies that valid-time periods of the table rows cannot overlap.
ANSI Compliance
This statement is ANSI SQL:2011 compliant.
Usage Notes
- If valid_time_periodWITHOUT OVERLAPS is not specified, the constraint acts as a nontemporal constraint and imposes uniqueness on the values disregarding whether valid-time periods overlap.
- If the table includes a SYSTEM_TIME derived period column, the start and end column components of the derived period column cannot be included in the column_list.
Example: Temporal Primary Key Constraint on a Valid-Time Table
CREATE MULTISET TABLE employee_vt_pk ( eid INTEGER NOT NULL, ename VARCHAR(5) NOT NULL, terms VARCHAR(5), job_start DATE NOT NULL, job_end DATE NOT NULL, PERIOD FOR job_dur(job_start,job_end) AS VALIDTIME, PRIMARY KEY(deptno, job_dur WITHOUT OVERLAPS) ) PRIMARY INDEX (eid);