Constraints check one row at a time: a CHECK sees only the row being changed, and a foreign key only the row it references. Rules that span rows or tables, such as "no crew may be assigned to a cancelled flight", have always needed triggers or application code. An assertion, new in Oracle AI Database 26ai, declares such a rule once, as a condition that must always be true, and Oracle checks it on every change to the tables involved.
Code for This Guide
The main examples are in the examples/schema-objects folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
create assertion [if not exists] name check (condition)
[deferrable | not deferrable] [initially {immediate | deferred}]The condition is usually NOT EXISTS with a subquery that finds violating rows.
No Crew on Cancelled Flights
Example:
create assertion no_crew_on_cancelled_flights check (
not exists (select null
from crew_assignments c, flights f
where f.flight_id = c.flight_id
and f.status = 'CANCELLED')
);
insert into crew_assignments (flight_id, employee_id, crew_role)
select flight_id, 112, 'CAPTAIN' from flights where status = 'CANCELLED'
fetch first 1 row only;Output:
Assertion NO_CREW_ON_CANCELLED_FLIGHTS created. Error starting at line : 8 In command - insert into crew_assignments (flight_id, employee_id, crew_role) select flight_id, 112, 'CAPTAIN' from flights where status = 'CANCELLED' fetch first 1 row only Error at Command Line : 8 Column : 13 Error report - SQL Error: ORA-08601: Assertion (NIMBUS.NO_CREW_ON_CANCELLED_FLIGHTS) violated.
Assigning a captain to a cancelled flight fails with ORA-08601, which names the assertion. The rule involves two tables, CREW_ASSIGNMENTS and FLIGHTS, which no single-table constraint could express. The example rolls back and drops the assertion afterward.
Restrictions
The condition cannot use aggregates:
Example:
create assertion max_two_captains check (
not exists (select null from crew_assignments
where crew_role = 'CAPTAIN'
group by flight_id having count(*) > 2)
);Output:
Error starting at line : 1
In command -
create assertion max_two_captains check (
not exists (select null from crew_assignments
where crew_role = 'CAPTAIN'
group by flight_id having count(*) > 2)
)
Error report -
ORA-08689: CREATE ASSERTION failed
ORA-08661: Aggregates are not supported.Nor can it use ANSI join syntax (ORA-08735): write joins with commas and WHERE, as the working example does. Rules about counts, such as at most two captains per flight, still need another approach, such as a trigger or a materialized view with a constraint.
Things to Know
- Like constraints, assertions can be DEFERRABLE and checked at commit.
- Each assertion has a name that error messages report, so name them after the rule they enforce.
Related Guides
Conclusion
Assertions enforce conditions across rows and tables, declared once with CREATE ASSERTION and checked by Oracle on every relevant change. Write them as NOT EXISTS conditions with comma joins and no aggregates.
