CREATE TABLE Statement (ANSI System-Time Table Form) | Teradata Vantage - 17.00 - CREATE TABLE (ANSI System-Time Table Form) - Teradata Database

Teradata Vantageā„¢ - ANSI Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1186-170K

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;