{"id":15957,"date":"2026-02-23T17:24:15","date_gmt":"2026-02-23T16:24:15","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=15957"},"modified":"2026-03-31T08:36:18","modified_gmt":"2026-03-31T06:36:18","slug":"enforcing-acyclic-hierarchies-with-sql-assertions","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2026\/02\/23\/enforcing-acyclic-hierarchies-with-sql-assertions\/","title":{"rendered":"Enforcing Acyclic Hierarchies With SQL Assertions"},"content":{"rendered":"\n
\n

<\/p>\n<\/blockquote>\n\n\n\n

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

Referential integrity constraints have been available since version 7 of the Oracle Database. While these constraints ensure that referenced rows exist, they cannot express more complex integrity rules that span multiple rows or tables. Check constraints are similarly limited, as they can only validate conditions on the columns of a single row.<\/p>\n\n\n\n

The latest version of the Oracle AI Database (23.26.1) introduced SQL Assertions, enabling us to define cross-row and cross-table checks declaratively.<\/p>\n\n\n\n

SQL Assertions support a wide range of use cases where data must satisfy global consistency rules. One particularly interesting example is ensuring that hierarchical structures remain free of cycles.<\/p>\n\n\n\n

In this blog post, I will demonstrate how SQL Assertions can detect and prevent cycles in hierarchical data structures.<\/p>\n\n\n\n

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

For the examples in this blog post, I used an 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>