Tales From A Lazy Fat DBA

If you’ve been working with Oracle for a while, you’ve probably hit a wall with constraints at some point. Primary keys, foreign keys, check constraints .. they’re great, but they all have one thing in common: they only work within a single row, or between two specific tables in a fixed relationship.

What happens when your business rule spans multiple rows? Or crosses two unrelated tables? You write a trigger. And then you spend the next hour making sure the trigger fires correctly on INSERT, UPDATE, and DELETE. And then six months later someone disables it during a data load and forgets to re-enable it. And then your data is a mess.

The idea is simple: define a boolean condition at the schema level, and the database guarantees that condition is always true, no matter what DML runs. Think of it as a check constraint, but with no limits … it can look across rows, across tables, anything.

The problem? For 30 + years, no major database engine actually implemented it. It existed in textbooks, in SQL courses, in Oracle certification material with a footnote saying “not supported” .. but never in production.

Until now ….. Oracle AI Database 26ai (Release Update 23.26.1, January 2026) is the first major database engine to ship SQL Assertions. That’s not marketing. That’s genuinely a first in this industry.

An assertion is a schema-level integrity constraint that contains a boolean SQL expression. The database evaluates that expression after every relevant DML operation and rolls back the statement if it returns false. The simplest way to think about it: a CHECK constraint which is more powerfull. A CHECK constraint can only see the row being modified. An assertion can see anything … the whole table, multiple tables, aggregates, subqueries.

CREATE ASSERTION assertion_name
CHECK ( <your boolean SQL condition here> )
[ DEFERRABLE | NOT DEFERRABLE ]
[ INITIALLY IMMEDIATE | INITIALLY DEFERRED ];
```

The moment any DML would violate the condition, Oracle raises:
```
ORA-08601: SQL assertion (SCHEMA.ASSERTION_NAME) violated.

And the statement rolls back. Clean, declarative, engine-enforced.

Lets discuss a real scenario that show where traditional constraints fail and assertions step in.

You have a FLIGHTS table with a capacity column and a SEATS table with one row per assigned seat per flight. Business rule: the number of rows in SEATS for a given flight can never exceed the flight’s capacity.

CREATE ASSERTION no_overbooking
CHECK (
  NOT EXISTS (
    SELECT f.flight_id
    FROM   flights f
    WHERE  (SELECT COUNT(*) FROM seats s WHERE s.flight_id = f.flight_id)
           > f.capacity
  )
);

Try to insert a seat assignment that pushes a flight past capacity …

INSERT INTO seats (seat_id, flight_id, passenger_id)
VALUES (9999, 101, 4455);

-- ORA-08601: SQL assertion (APP.NO_OVERBOOKING) violated.

The check happens inside the database engine, not in your booking API. That means it works even if someone runs a direct INSERT from SQL Developer, a data migration script, or any other path that bypasses your application layer.

Assertions work exactly like other Oracle constraints in terms of state management. You have four states:

You can also make assertions deferrable, which is critical when you need to temporarily violate a rule mid-transaction and fix it before commit:

-- Deferrable assertion: checked at COMMIT, not after each statement
CREATE ASSERTION president_salary_range
CHECK (
  EXISTS (
    SELECT 1 FROM emp
    WHERE  job = 'PRESIDENT'
    AND    sal BETWEEN 4000 AND 10000
  )
)
DEFERRABLE INITIALLY DEFERRED;

This means within a transaction you can update the president’s salary to something temporarily outside the range …as long as it’s corrected before COMMIT. The assertion only fires at commit time. For non-deferrable assertions (the default), the check happens immediately after each DML statement.

But there are some limitations as well … Oracle’s implementation is solid but it’s version 1. These are real constraints to be aware of:

No non-deterministic functions : You cannot use SYSDATE, SYSTIMESTAMP, SYS_CONTEXT, USER, USERENV, CURRENT_SCHEMA, or any session-dependent context. Assertions must evaluate identically regardless of who runs the DML or when.
No PL/SQL : You can’t call PL/SQL functions inside an assertion … not even deterministic ones. Pure SQL only.
Read Committed only : Assertions are not supported under SERIALIZABLE isolation level.
No synonyms for cross schema references: If your assertion references tables in another schema, you must use the explicit SCHEMA.TABLE_NAME notation. Synonyms are not resolved.
No GROUP BY or analytic functions: At least in the current release. Aggregate conditions need to be expressed using subqueries.
No EXCEPTIONS INTO clause: Unlike constraint violations on existing data, there’s no way to route assertion violations to an exceptions table ..yet.

So, what’s the enginnering behind it, what happens internally ?>

When you create an assertion, Oracle creates internal change tracking auxiliary tables prefixed ORA$SA$TE_ … alongside your regular tables. These are internal global temporary tables with restricted read access (ORA-08709 if you try to query them directly). Their job is to track which rows changed in a DML operation so Oracle doesn’t have to re-evaluate the entire assertion from scratch on every statement.

You can see the locking strategy Oracle uses via the dictionary view, here are some of the related views.

-- Privilege to create assertions in your own schema
GRANT CREATE ASSERTION TO your_user;

-- Or just use the built-in developer role
GRANT DB_DEVELOPER_ROLE TO your_user;

-- Dictionary views
SELECT assertion_name, status, validated, deferrable, deferred
FROM   user_assertions;

SELECT * FROM user_assertion_dependencies;
SELECT * FROM user_assertion_lock_matrix;

For example, if your assertion involves EMPNO as a join column, Oracle knows it only needs to revalidate the assertion for the specific EMPNO values touched by the DML not the entire table. This is what makes assertions practical at scale. It’s essentially fine-grained, row-level re-validation rather than a full table scan on every INSERT or UPDATE.

If you manage data integrity rules through triggers today .. especially cross-table or multi row rules ..this is worth a serious look. The syntax is clean, the behavior is predictable, and Oracle’s internal change-tracking implementation means it’s not just a theoretical feature that kills performance on every write.

It’s real, it’s on-prem available, and it’s the most quietly significant data integrity improvement Oracle has shipped in a very long time. Oracle AI Database 26ai, Release Update 23.26.1 (January 2026) and later. On-premises Linux x86-64 supported.

Hope It Helped!
Prashant Dixit