To add a transaction-time column to a nontemporal or valid-time table, use the ALTER TABLE statement.
Temporal tables cannot have unique primary indexes. If the original nontemporal table has a unique primary index, use ALTER TABLE to modify the primary index to nonunique prior to adding the temporal column. Uniqueness can be applied to a temporal table using other constraints. For more information, see Using Constraints with Temporal Tables.
Example: Creating a Transaction-Time Column Based on Two Existing Timestamp Columns
If a nontemporal table has two pre-existing TIMESTAMP(6) WITH TIME ZONE columns that represent the beginning and end bounds of a period of time, you can add a derived period column to the table based on these columns. The derived period column can serve as a transaction-time column, converting the table to a temporal table.
Given a table that was originally created with the following DDL statement:
CREATE MULTISET TABLE Policy( Policy_ID INTEGER, Customer_ID INTEGER, Policy_Type CHAR(2) NOT NULL, Policy_Details CHAR(40), Policy_Tx_Begin TIMESTAMP(6) WITH TIME ZONE NOT NULL, Policy_Tx_End TIMESTAMP(6) WITH TIME ZONE NOT NULL ) PRIMARY INDEX(Policy_ID);
Prior to converting the table to a temporal table, existing constraints should be noted, then dropped. The table could be converted to a transaction-time table using the following statement:
ALTER TABLE Policy ADD PERIOD FOR Policy_Tx_Duration (Policy_Tx_Begin,Policy_Tx_End) AS TRANSACTIONTIME;
After creating the temporal table, the constraints that were dropped can be reapplied.
Example: Adding a Period Data Type Transaction-Time Column
Consider the following nontemporal table definition that lacks duration data:
CREATE MULTISET TABLE Customer ( Customer_Name VARCHAR(40), Customer_ID INTEGER, Customer_Address VARCHAR(80), Customer_Phone VARCHAR(12) ) PRIMARY INDEX (Customer_ID);
The following statement adds a transaction-time column to the Customer table:
ALTER TABLE Customer ADD Customer_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL AS TRANSACTIONTIME;
Converting a Period Column to a Transaction-Time Column
If you have a table that includes a column of type PERIOD(TIMESTAMP(6) WITH ZONE), take the following steps to convert the existing Period column to a transaction-time column:
- Note all the constraint information on the original table.
- Drop all the constraints on the original table.
- Grant NONTEMPORAL privilege to the user on the table.
- ALTER TABLE to add a transaction-time column.
- Submit NONTEMPORAL UPDATE to set the new transaction-time column with the existing column value being converted.
- ALTER TABLE to drop the existing Period column.
- ALTER TABLE to rename the transaction-time column with the name of the dropped column.
- Create all the previously dropped constraints with the desired transaction-time qualifier.