Oracle 26ai Assertions

The use case

I recently had an interesting problem, that couldn't get resolved by a simple foreign key constraint. A key in one of our tables could have come from any of two other tables. And it should have been a valid key that already existed there.

Think of it as a foreign key. It would have been the perfect solution if the source table was just one. But a FOREIGN KEY referencing two different tables?

CHECK constraints can't help either, since they only see the columns of the same row.

Here is a test setup, similar to ours (not a perfect data model though, but great to show the kind of problem we needed to solve):

We have a schema with two different tables holding the information about aircraft - owned aircraft and leased aircraft:

• OWNED_AIRCRAFT - aircraft that the company owns. Aircraft_IDs are prefixed OWN-.

• LEASED_AIRCRAFT - aircraft that are leased from a third party. Aircraft_IDs are prefixed LSE-.

• A FLIGHT_ASSIGNMENTS table that keeps record of which aircraft flies which flight, using an AIRCRAFT_ID column. That ID should always resolve to exactly one of the two source tables:

---

• If AIRCRAFT_ID does not exist in either OWNED_AIRCRAFT or LEASED_AIRCRAFT - it's a data entry error.

• Also, AIRCRAFT_ID can't be null.

Before the assertion

To help me demonstrate the Assertions better, I have inserted 4 records - two aircraft in each of owned_aircraft and leased_aircraft tables:

insert into owned_aircraft (aircraft_id, tail_number, owner_name) values ('OWN-100', 'N100AB', 'Northwind Aviation');
insert into owned_aircraft (aircraft_id, tail_number, owner_name) values ('OWN-101', 'N101AB', 'Northwind Aviation');
insert into leased_aircraft (aircraft_id, tail_number, lessor_name) values ('LSE-200', 'N200LS', 'SkyLease Partners');
insert into leased_aircraft (aircraft_id, tail_number, lessor_name) values ('LSE-207', 'N207LS', 'SkyLease Partners');

With no assertion in place, the invalid data gets inserted successfully into flight_assignments, which is wrong (from business point of view) and should not be allowed:

-- Unknown entry: XYZ-999 does not exist in any of the two tables.
insert into flight_assignments (aircraft_id, flight_number, flight_date)
values ('XYZ-999', 'AS 100', sysdate);

The statement commits without any error, because nothing checks the cross-table reference.

The assertion

Aircraft_ID in any of the parent tables

create assertion flight_aircraft_one_source
check (
    not exists (
        select 'An aircraft that does not exist in Owned or Leased aircraft tables'
        from flight_assignments fa
        where not exists (select 'owned'  from owned_aircraft  oa where oa.aircraft_id = fa.aircraft_id)
          and not exists (select 'leased' from leased_aircraft la where la.aircraft_id = fa.aircraft_id)
    )
);

This is the so-called existential expression - one of the two forms CREATE ASSERTION supports (the other one is the newer ALL ... SATISFY form). You can read the above expression as:

"It must never be true that some flight's aircraft is in neither owned_aircraft nor leased_aircraft."

Aircraft_ID UNIQUE for both parent tables

create assertion owned_leased_aircraft_id_unique
check (
    not exists (
        select 'Aircraft is in both owned and leased'
        from owned_aircraft oa
        where exists (select 'leased' from leased_aircraft la where la.aircraft_id = oa.aircraft_id)
    )
);

"Aircraft_id should be unique for owned_aircraft and leased_aircraft. Think of it as a joint (or extended) Primary Key (or Unique Key), that spans both tables"

After the assertion

Testing the flight_aircraft_one_source Assertion

-- Expected: success. OWN-101 exists only in OWNED_AIRCRAFT.
insert into flight_assignments (aircraft_id, flight_number, flight_date)
values ('OWN-101', 'AS 204', date '2026-10-03');

-- Expected: success. LSE-207 exists only in LEASED_AIRCRAFT.
insert into flight_assignments (aircraft_id, flight_number, flight_date)
values ('LSE-207', 'AS 318', date '2026-10-03');

-- Expected: fail with ORA-08601 (aircraft does not exist in neither of the parent tables).
insert into flight_assignments (aircraft_id, flight_number, flight_date)
values ('XYZ-999', 'AS 412', date '2026-10-04');

The last statement fails with:

SQL Error: ORA-08601: Assertion (AGENTS.FLIGHT_AIRCRAFT_ONE_SOURCE) violated.

And now something even more interesting! You get the same error if you try to delete a row with a referenced aircraft. The assertion is re-evaluated whenever any table it depends on changes, not just FLIGHT_ASSIGNMENTS.

-- Expected: fail with ORA-08601. OWN-101 is referenced by flight "AS 204". Deleting it would create an orphaned record in flight_assignments.
delete from owned_aircraft where aircraft_id = 'OWN-101';

And once again it fails with:

SQL Error: ORA-08601: Assertion (AGENTS.FLIGHT_AIRCRAFT_ONE_SOURCE) violated.

Testing the owned_leased_aircraft_id_unique Assertion

-- Expected: fail with ORA-08601. LSE-207 already exists in leased_aircraft and should fail to get inserted into owned_aircraft.
insert into owned_aircraft (aircraft_id, tail_number, owner_name) values ('LSE-207', 'N207AB', 'Northwind Aviation');

Actual result is the following (as expected):

SQL Error: ORA-08601: Assertion (AGENTS.FLIGHT_AIRCRAFT_ONE_SOURCE) violated.

The SQLcl Project gap

If you manage your database schema changes using SQLcl Project and try to bring the above assertions into your repository using project export and project stage, you will notice that SQLcl does not pick it up.

The list of supported objects - tables, constraints, indexes, views, packages, etc. does not include ASSERTIONS. Usually such object types (or any other custom scripts, such as inserts, updates, deletes of data) should be added using project stage add-custom.

The database actually keeps the information about all your Assertions, including the DDL script for creating it (see definition_sql column below).

select user as owner,
       assertion_name,
       status,
       deferrable,
       deferred,
       validated,
       definition_sql
  from user_assertions
 order by assertion_name;

What's missing is the automatic object discovery in SQLcl Project and adding the scripts automatically to your repo.

An AI skill to close the gap

Living in a world of AI Agents, it makes sense to have a skill, alongside the other Oracle skills, capable of doing this missing part. I'm confident that it will soon be supported straight into SQLcl Project soon.

Until then, I suggest you check what Alex Kluev has created - we did test it on various platforms, Operating Systems, AI Agents - they all managed to produce the missing Assertions SQL in our SQLcl Project repository.

https://alexonapex.com/blog/2026/10/14/sql-assertions-sqlcl-project-agent-skill/

Limitations to keep in mind

• CREATE ASSERTION does not support a long list of constructs: aggregates, GROUP BY/HAVING, set operators, scalar subqueries, views, synonyms and more. Existential expressions nest up to three [NOT] EXISTS levels deep, universal (ALL ... SATISFY) expressions up to two.

• Once created, the deferrability of an assertion (DEFERRABLE / NOT DEFERRABLE) cannot be altered. You need to drop and re-create it.

References

• CREATE ASSERTION syntax reference

• Assertions in Oracle AI Database 26ai

• SQLcl Project supported objects and add-custom staging

• Skill for adding Assertions when working with SQLcl Project