To add a valid-time column to a nontemporal or transaction-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: Adding a Period Data Type Valid-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 valid-time column to the Customer table:
ALTER TABLE Customer ADD Customer_Validity PERIOD(DATE) AS VALIDTIME;
Converting a Period Column to a Valid-Time Column
If you have a table that defines a Period column, take the following steps to convert the existing Period column to a valid-time column:
- Note all the constraint information on the table.
- Drop all the constraints.
- ALTER TABLE to add a new valid-time column.
- Submit NONSEQUENCED VALIDTIME update to set the new valid-time column with the existing period column value.
- ALTER TABLE to drop the existing Period column from the table.
- ALTER TABLE to rename the valid-time column with the name of the dropped column.
- Create all of the previously dropped constraints with the desired valid-time qualifier.