Purpose
Create a new ANSI system-time table.
Syntax
CREATE MULTISET TABLE table_name ( sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL GENERATED ALWAYS AS ROW START, sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME ( sys_start, sys_end ) ) WITH SYSTEM VERSIONING [;]
- table_name
- The name of the system-time table. May include database qualifier.
- sys_start
- The name of the column that will store the beginning bound of the system-time period.
- GENERATED ALWAYS AS ROW START
- Required attribute for column that defines the beginning bound of system-time period.
- sys_end
- The name of the column that will store the ending bound of the system-time period.
- GENERATED ALWAYS AS ROW END
- Required attribute for column that defines the ending bound of system-time period.
- PERIOD FOR SYSTEM_TIME
- Creates the system-time derived period column.
- SYSTEM VERSIONING
- Required attribute for system-time temporal tables. Must be the last clause in the CREATE TABLE statement.
ANSI Compliance
This statement is ANSI SQL:2011 compliant.
Usage Notes
- The sys_start column must be defined as NOT NULL and GENERATED ALWAYS AS ROW START. The sys_end column must be defined as NOT NULL and GENERATED ALWAYS AS ROW END.
- The GENERATED ALWAYS AS ROW START or END attributes cannot be dropped from the definitions of the columns that constitute the SYSTEM_TIME derived period column.
- Component columns of a system-time derived period column cannot be part of the primary index.
- To function as a temporal table, the system-time table must be defined WITH SYSTEM VERSIONING. (Valid-time tables do not have system versioning.)
- A table can have only one system-time period definition.
- A system-time table cannot be a queue, error, global temporary, global temporary trace, or volatile table.
- CHECK constraints on tables with system time cannot include the start or end columns of the system-time period.
- The start and end columns of the system-time period cannot be part of a primary or foreign key of a temporal referential constraint.
- System-time tables cannot act as source tables in CREATE TABLE AS statements.
- Statistics cannot be collected on the system-time derived period column, but they can be collected on the component start and end time columns.
- Algorithmic compression (ALC) is not allowed on DateTime columns that act as the beginning and ending bound values of a temporal derived period column.
Example: Creating a System-Time Table
The following example creates a system-time table and includes the system versioning clause, which is required to make the table a temporal table.
CREATE MULTISET TABLE employee_system_time ( eid INTEGER NOT NULL, name VARCHAR(10)NOT NULL, deptno INTEGER NOT NULL, sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL GENERATED ALWAYS AS ROW START, sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME(sys_start, sys_end) ) PRIMARY INDEX (eid) WITH SYSTEM VERSIONING;