TL;DR: A simple use-case for SQL-Assertions: Adding a data-related constraint to an arc-relation. Demo using the Emp-Dept schema.
Summary up front:
Use SQL-Assertions to add a data-related constraint covering multiple tables.
Curious? The link to the Source ( tst_ass.sql ). You can Immediately run this file but dont forget to first run demobld.sql or utlsampl.sql to get your emp-dept tables.
-- -- -- Background: I wanted to try this... -- -- --
Oracle 26ai (link ) as we write in Feb-2026 it is at release 26.1. And it has this new feature: SQL-Assertions (link to oracle).
I'll put some more links to "SQL Assertions" at the bottom. The Actual Documentation and several Other sources can explain it all better than I can.
As the feature became GA-available, and I got to read about it, and I kept viewing presentations about it.. It got to the front of my mind. I Wanted to Try it..
And when walking around with a Neat Solution looking for an interesting Problem, you inevitable find one...
-- -- Use-case: store audit-findings at the level of Emp, Dept, Total, or Any. -- --
Imagine you want to store+report audit-findings in a table. Those findings can be at detail-level (say, for a single Emp), or at higher level (e.g. at Dept level), or even for the whole Enterprise (e.g. Total). But you dont want to allow the storing of a finding at a non-suitable level.
Then you have an auditor wants to record items (automatically) and check for auditable-conditions. Those checks result in "Findings", and we want to store them in tables.
Like, for example:
- Max-commission: applies to Emp (and can not apply to other levels).
- Min and max-headcount: applies to Dept (and not to any other levels)
- Total headcount: employees in the organisation: applies to Total.
- Max reports to a manager: how many Empoloyees under a manager.
- ... anyone with more examples, let me know ...
Our real-world case is a little more complicated, but I hope you get the idea: Definitions determine at what level a finding applies, and should be stored. When creating the Definition-record to define an audit-item, you can determine whether that definition can apply to a certain level: Emp, Dept, All, or Any.
-- -- -- Entities: Definition and Finding -- -- --
The problem description leads to two entities: DEFinition and FiNDing.
Audit_Definition (A_DEF):
A description of a Finding. In our case we will even try to store an SQL-statement to generate a finding so it can be run at regular intervals...
Audit_Finding (A_FND):
The result of a check or sample, stored as a record. Such a finding should be linked to the Definition, it's parent-entity. But it might also be linked to some other entity to which it relates: an Emp, a Dept, or the Totality of the Entreprise, or maybe to "nothing" or some as-yet undefined entity...
The other two tables are the classic Emp and Dept from the oracle-demo schema.
Those entities have the following Relations:
- Emp Must Have a Dept (FK, parent) - this is the classic one.
- Finding Must Have a Definition (FK, parent).
- Finding Can Have an Emp (FK, parent), if the finding is at Emp-level.
- Finding Can Have a Dept (FK, parent), if the finding is at Dept Level.
- Finding Can Not Have Both Empt and Dept, an Xor-Arc relationship
We'll try to use an Assertion to prevent invalid combinations to go into the Findings-table.
-- -- -- The data-model, a simple ERD with one arc-relation. -- -- --
To illustrate, I'll present the simplified data-model as an ERD (and drawing with whatever tools always takes more time thant you think...
My datamodel looks like this:
I know there exist other modelling methods as well, but this is what I mostly use. Tables, with relations defined by lines with Os, and the crow-foot. And with the Arc denoting 1 of several... The exact drawing-conventions is something for another blog/discussion.
(<smallprint... yes, modern systems would probably use some JSON-string to record this kind of data, and probably would not build in this kind of complicated constraint into an assertion.. but IMHO they miss an opportunity for Data-Integrity /smallprint>)
-- -- The Tables, the DDL -- --
The two tables Emp and Dept are from the known demo: Emp and Dept:
The Audit data and the Assertion will use a table for Definitions and a table for Findings: A_def and A_fnd.
This table holds the definitions... (there is room for some refinement, such as storing an SQL statement or an API call in the record)
Next is the table for Findings:
Notice the constraints on the findings-table: we can sort-of specify the x-arc, but not quite the additional constraint we need...
Finally, the Assertion, it looks like this:
A careful observer would say: The OR-condition in the assertion could be used to create Two separate assertions. That is something I want to examine and play with later...
-- -- -- Now Insert some data (and generate the errors...) -- -- --
The demo file contains several error-free inserts, and 2 stmnts that trigger the Assertion...
Here is the first error: we try to insert a Finding that is supposed to be at Dept level, but we try to attribute it to an empno:
There is the Assertion-in-action.
In trying to provoke the error, I also discovered that the FK constraint seemed to fire before the Assertion. Which, I think, makes sense.
Now try to insert an emp-level finding with a deptno:
And voila: The Assertion again prevents the insert.
The demo-file ( tst_ass.sql ) contains a few more statement: Do Try it.
And if you want to test further: run the demo without creating the assertion, and see the (unwanted) data go in.
Notice that the error-mesage is the same, regardless of which part of the OR triggers the error. First remark from an Oracle-friend: If you create two separate Assertions, you get the benefit of more Detailed Error Messages. Something to remember.
And there may also be performance considerations for choosing larger or smaller assertions. Maybe Later...
-- -- -- Conclusion: I found "a case" -- -- --
I think I found a very Simple case for using SQL-Assertions. This case cold have been done with Triggers but that would haver resulted in much more complicated code divided over several triggers on the tables involved.
The Assertion (or two assertions of you prefer that) does this more elegant, and probably more efficient.
And in case you missed it above: the link to sourcefile tst_ass.sql
-- -- -- -- -- End of this blogpost, for now -- -- -- -- --
That is not all...
More then other blogs I've done, there is a surprising amount of notes and links that deserve keeping + Sharing, hence some appendices...
-- -- -- -- -- Appendix 1 : Possible Future items, Several -- -- -- -- --
- Refine the assertion into two (or more) and use assertion-names to better "Clarify" the error when the assertion pops (First suggestion from a proofreader)
- Highlight the fact that FKs and Check-constraints are verified before Assertions ? (is that correct? I think it is...).
- Show that data can not be modified or deleted to invalidate an assertion.
- Investigate (verify) the dependencies between Assertions and Tables.
- Show the internal tables used to trace and check assertions (and why are those visible in v23.26.1 ?)
- Examine user_assertions and other related views, such as the "dependencies".
- Operation 0 succeeded (older SQL*Plus versions).
- Try testing a ridiculous check-condition... or som heavy SQL.. what happens?
-- -- -- -- -- -- Appendix 2: Links to SQL-Assertions -- -- -- -- --
Despite all the saved notes... I still feel like I dont have a good intro on Assertions.
When trying to find non-Oracle sources, it was surprisingly difficult to find good text on SQL-Assertions. The Assertion is part of SQL since the SQL92 standard. Yes, the concept is That Old. But the implementations are lagging. And so is the set of documentation and examples...
To get the official ISO or ANSI standard text on SQL92, you need to purchase it.. (Huh??). But I found the almost-official text, an old page from Carnegie-Mellon University:
https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
(check section 4.10.4 on Assertions, a very brief text...)
This link to a book-chapter on Assertions:
https://csci3030u.science.ontariotechu.ca/chapter_7/chapter_7_section_4.html
And this (recommended reading?) on Constraints and Asssersions:
https://sql-99.readthedocs.io/en/latest/chapters/20.html
And a CS course from U of Virginia, this slide-deck about SQL-Assertions
https://www.cs.virginia.edu/~up3f/cs4750/slides/4750meet19-SQL-assertion.pdf
But to get Assertions better ingrained in the design and build of datatabase-systems, I think I need to...
Call on all Bloggers and other CCs: Collect + Write more on Assertions ??
-- -- -- -- -- -- Appendix 3 : Remarks on demo-scripts... -- -- -- -- --
Allow me some Friendly Remarks to the folks at Oracle, not related to the assertion-demo:
- Script demobld.sql is missing in oracle 26ai ?? It used to be under $ORALE_HOME/sqlplus/admin/demobld ? The alternative script is now located at $ORACLE_HOME/rdbms/admin/utlsampl.sql... took me some searching..
- The utlsampl.sql does a connect as scott/tiger (hardcoded password, and no SID or service specified ??) In my case, the data once accidentally ended up under the SYS-user in the CDB$ROOT (my bad for not paying attention...)
Luckily, I keep a version of demobld.sql in my own toolkit-collection of scripts-to-carry. Both on github and on my trusty USB-stick (26yrs ago that was a floppy-disk, some script have survived the teeth of time).
-- -- -- -- -- more notes -- -- -- -- --
To get an ERD, I tried submitting text and code to chatgpt and asked it to draw me an ERD (four tables, five relations, one of which is an X-Arc, how hard can it be...).
First results were abysmal, but chatgpt asked me if I wanted x, y or z as improvement. None of the improvements were any good. And all the followup questions seemed geared towards making me spoil my remaining free questions... I wont blame openAI for trying to make a buck, but I am Not Impressed.
An ancient TOAD version did a much better job.
My Final ERDs for this blog: I used the online version of Draw.io (link). And Quite Happy with that. I probably will use more of that tool
-- -- -- -- -- End of this Blogpost, for real -- -- -- -- --
