If a default value is specified for a new valid-time column, the column is populated with the specified value. Otherwise, the new column is populated as follows:
| IF the valid-time column has a … | THEN the column is populated with a value of … |
|---|---|
| PERIOD(DATE) data type | PERIOD(TEMPORAL_DATE, UNTIL_CHANGED). |
| PERIOD(TIMESTAMP) data type | PERIOD(TEMPORAL_TIMESTAMP, UNTIL_CHANGED). The precision and time zone values are set depending on the data type of the new column. |
In addition to the rules for specifying a valid-time column specified in CREATE TABLE/CREATE TABLE ... AS (Temporal Forms), the following rules apply when using ALTER TABLE to add a valid-time column to an existing table:
- If the table has a transaction-time column, the ALTER TABLE statement must specify the NONTEMPORAL prefix. This requires the NONTEMPORAL privilege on the table.
- The table cannot have a UPI.
- If it does, first use ALTER TABLE table_name MODIFY NOT UNIQUE, which is described in SQL Data Definition Language, to modify the PI to be non-unique.
- The table may have a NUPI, or it can have no primary index.
- Existing CHECK constraints become current constraints in the valid-time dimension. An error is reported if there are any other types of constraints. See Using Constraints with Temporal Tables.
- Any join indexes defined on the table must be dropped before the table can be made a valid-time table.
- The table cannot be the subject table of an existing trigger.
- Existing views, macros, or triggered action statements that reference the table but do not specify a valid-time qualifier in the statement referencing the table must be modified to add a valid-time qualifier.
If an executing stored procedure includes an SQL statement that references the table being altered, and no explicit qualifier is specified in the SQL, the compile time qualifier is applied to the SQL.
The partitioning for the table cannot be altered to be partitioned on the added valid-time column unless the table is empty.