History tables

Each system-period temporal table is tied to a history table. When a row is updated or deleted from a system-period temporal table, the database manager inserts a copy of the old row into its associated history table with an updated row-end timestamp. This storage of prior system-period temporal table data gives you the ability to retrieve data from past points in time.

The columns in the history table and system-period temporal table must have the exact same names, order, and data types. You can create a history table with the same names and definitions as the columns of the system-period temporal table by using the LIKE clause of the CREATE TABLE statement. For example:

CREATE TABLE hist_policy_info LIKE policy_info;

A history table is subject to the following rules and restrictions when versioning is enabled:

  • A history table cannot explicitly be dropped. It can only implicitly be dropped when the associated system-period temporal table is dropped.
  • History table columns cannot explicitly be added, dropped, or changed.
  • A history table cannot be defined as parent, child, or self-referencing in a referential constraint.

Once versioning is established, a change to a system-period temporal table causes an implicit corresponding change to the history table. For example, if a system-period temporal table is altered to add a column, the same column is added to the history table.

Since deleting data in a history table might jeopardize your ability to audit the system-period temporal table data history, you should restrict access to a history table to protect its data.