Schema changes

In order to maintain the integrity of the relationship between the system-period temporal table and its associated history table, only certain changes to the schema of a system-period temporal table are permitted. Any changes that would result in the loss of data are restricted.

You can make the following changes to your system-period temporal tables. These changes are implicitly propagated to the associated history table if you have the appropriate privileges. These changes cannot be explicitly made to the history table.
  • ADD COLUMN (except generated columns)
  • RENAME COLUMN
  • ALTER COLUMN (in cases where no history data is lost). For example, altering the data type of a column from VARCHAR(50) to VARCHAR(100), or from INTEGER to DECIMAL is permitted. However, the reverse change from VARCHAR(100) to VARCHAR(50), or from DECIMAL to INTEGER is blocked because it would reduce the length or precision of a column and likely cause a loss of data.
You cannot make the following changes to your system-period temporal tables because data would likely be lost:
  • DROP COLUMN
  • ADD COLUMN (generated)
  • ALTER COLUMN (in cases where history data might be lost). For example, altering the data type of a column from VARCHAR(50) to VARCHAR(100), or from INTEGER to DECIMAL is permitted. However, the reverse change from VARCHAR(100) to VARCHAR(50), or from DECIMAL to INTEGER is blocked because it would reduce the length or precision of a column.
Versioning establishes a link between your system-period temporal table and its associated history table. When versioning is active, certain ALTER TABLE operations are blocked on system-period temporal tables and history tables.
  • ALTER TABLE DROP PERIOD
  • ALTER TABLE ADD MATERIALIZED
  • ALTER TABLE ACTIVATE NOT LOGGED INITIALLY
  • ALTER TABLE ADD SECURITY POLICY
  • ALTER TABLE DROP SECURITY POLICY
  • ALTER TABLE SECURED WITH ALTER
ALTER TABLE operations that are not shown in the previous list are supported, but are not propagated from a system-period temporal table to its history table.

Additionally, RENAME INDEX and RENAME TABLE are supported, but are not propagated from a system-period temporal table to its history table.