{"id":16264,"date":"2026-03-09T15:45:31","date_gmt":"2026-03-09T14:45:31","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=16264"},"modified":"2026-03-10T11:50:01","modified_gmt":"2026-03-10T10:50:01","slug":"using-sql-assertions-to-enforce-temporal-data-integrity","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2026\/03\/09\/using-sql-assertions-to-enforce-temporal-data-integrity\/","title":{"rendered":"Using SQL Assertions to Enforce Temporal Data Integrity"},"content":{"rendered":"\n

Introduction<\/h2>\n\n\n\n

More than twelve years ago, I described the multi-temporal features of Oracle Database in this blog post<\/a>. In the conclusion, I wrote that I missed a temporal DML API, temporal integrity constraints, temporal joins, and temporal aggregations. These features are still missing today.<\/p>\n\n\n\n

However, in the latest version of Oracle AI Database (23.26.1), SQL assertions make it possible to define temporal integrity constraints manually. In this post, I demonstrate how.<\/p>\n\n\n\n

Data Model<\/h2>\n\n\n\n

There are various ways to model temporal data. In this post, I use the well-known non-temporal DEPT-EMP model as a basis and add a history table for each non-temporal table with a valid-time period<\/a> named vt<\/code> using the column vt_start<\/code> for the beginning of the period and vt_end<\/code> for the end of the period. Please note that Oracle Database uses half-open intervals for temporal periods and NULL<\/code> for -\u221e<\/code> and +\u221e<\/code>.<\/p>\n\n\n\n

Non-temporal tables store the most recent version of the data, which is kept in their history tables. This simplifies access to the latest version and provides the expected performance for non-temporal data. The disadvantage is that the API must maintain redundant data. Note that the latest version is not necessarily the current version. The current version is the one whose valid-time period contains the current date and time.<\/p>\n\n\n\n

This model versions all columns of the base table, potentially resulting in more rows in the history tables than necessary. Furthermore, the model does not support soft deletes. In other words, there is no is_deleted<\/code> flag. This makes managing data integrity easier. No need to keep reference and structure data just because some deleted rows still reference it. In any case, this is sufficient for this blog post.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

Setup<\/h2>\n\n\n\n

For the examples in this blog post, I used Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0.<\/p>\n\n\n\n

Connected as SYS<\/code>, I ran the following script to create the user DEMO<\/code> with the necessary privileges.<\/p>\n\n\n\n

1) DEMO user<\/span>