Since the SQL-92 standard, “Assertions” have existed, but no database engine had implemented them until now.
We could define a SQL Assertion as an advanced constraint that allows cross-checking across multiple tables or across rows in the same table. I give examples later.
In practice, this type of condition could only be implemented with triggers and, besides the complexity of the implementation logic, it was very difficult to guarantee its integrity and persistence. Not to mention that checking these conditions on every DML could considerably impact performance.
Every time I taught a SQL course and the topic came up, I always pointed out that “this was explained in PL/SQL”, mentioning triggers.
And then, when the time came to explain triggers and what they were useful for, I defined them as “a layer of PL/SQL attached to a table, sensitive to insert/update/delete, capable of recording audits or complex traces, or of maintaining advanced integrity restrictions across different tables”.
I was referring to these SQL Assertions and, in more than 25 years, I have not been able to teach this lesson because no database engine implemented them.
Until now.
SQL Assertions are, finally, a new feature of Oracle26ai. Be careful, only in version 23.26.1 (that is, with the January 2026 RU patch applied).
So let’s go all in.
Practical cases
Let us think about a first scenario for the flights dataset in which we can apply these SQL Assertions. If you want to generate the table model, you have it in Café Database’s GitHub repository here.
https://github.com/CafeDatabase/flights-dataset
We have FLIGHTS with different capacities. These can be 50, 60, 100 or 120 seats. Something as if we had four types of aircraft.
In the loaded model, we have, for each flight, as many seats in the SEATS table as the total capacity of the aircraft for that flight. A flight on an aircraft with capacity for 50 seats has 50 rows in the SEATS table. Flights with 100 seats have 100 rows in the SEATS table.
We could be interested in creating a restriction that validates that the total number of rows in the SEATS table assigned to each flight does not exceed that capacity.
And we need that restriction to always be met. Whether a change is made to the aircraft capacity, or if we add new seats to a given flight.
SEATS must be equal to or less than the AIRCRAFT_CAPACITY (column) of the FLIGHT.
Currently, the flights dataset is loaded by populating as many seats as the capacity of its flight. In this context, it should be possible for a 60-seat flight to have 58 or 59 on sale, but not 61.
CREATE ASSERTION capacity_consistent_with_seats
CHECK (
NOT EXISTS (
SELECT 'seat out of capacity'
FROM FLIGHTS f, SEATS s
WHERE s.FLIGHT_ID_FLIGHT = f.ID_FLIGHT
AND s.SEAT_ID > f.AIRCRAFT_CAPACITY
)
);SQL> CREATE ASSERTION capacity_consistent_with_seats
2 CHECK (
3 NOT EXISTS (
4 SELECT 'seat out of capacity'
5 FROM FLIGHTS f, SEATS s
6 WHERE s.FLIGHT_ID_FLIGHT = f.ID_FLIGHT
7 AND s.SEAT_ID > f.AIRCRAFT_CAPACITY
8 )
9 );
Assertion created.
SQL> update FLIGHTS set aircraft_capacity=60 where id_FLIGHT=92363;
update FLIGHTS set aircraft_capacity=60 where id_FLIGHT=92363
*
ERROR at line 1:
ORA-08601: SQL assertion (FLIGHTS.CAPACITY_CONSISTENT_WITH_SEATS) violated.
Help: https://docs.oracle.com/error-help/db/ora-08601/
But what happens if we want to allow the Assertion to be validated at transaction level? That is, allow the flight to change to a smaller capacity and, before doing the COMMIT, delete the additional seats.
In this case, just like with constraints, we have the DEFERRABLE INITIALLY DEFERRED clause.
SQL> CREATE ASSERTION capacity_consistent_with_seats 2 CHECK ( 3 NOT EXISTS ( 4 SELECT 'seat out of capacity' 5 FROM FLIGHTS f, SEATS s 6 WHERE s.FLIGHT_ID_FLIGHT = f.ID_FLIGHT 7 AND s.SEAT_ID > f.AIRCRAFT_CAPACITY 8 ) 9 ) DEFERRABLE INITIALLY DEFERRED; Assertion created.
This makes it possible to update a flight to a smaller capacity, but we must make sure before committing that we have removed the excess seats.
Let’s see it.
SQL> drop assertion capacity_consistent_with_seats;
Assertion dropped.
SQL> CREATE ASSERTION capacity_consistent_with_seats
2 CHECK (
3 NOT EXISTS (
4 SELECT 'seat out of capacity'
5 FROM FLIGHTS f, SEATS s
6 WHERE s.FLIGHT_ID_FLIGHT = f.ID_FLIGHT
7 AND s.SEAT_ID > f.AIRCRAFT_CAPACITY
8 )
9 ) DEFERRABLE INITIALLY DEFERRED;
Assertion created.
SQL> update FLIGHTS set aircraft_capacity=60 where id_FLIGHT=92363;
1 row updated.
SQL> delete from SEATS where SEAT_ID > 60 and flight_id_FLIGHT=92363;
40 rows deleted.
SQL> commit;
Commit complete.
SQL> update FLIGHTS set aircraft_capacity=50 where id_FLIGHT=92363;
1 row updated.
SQL> delete from SEATS where SEAT_ID > 51 and flight_id_FLIGHT=92363;
9 rows deleted.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-08601: SQL assertion (FLIGHTS.CAPACITY_CONSISTENT_WITH_SEATS) violated.
Help: https://docs.oracle.com/error-help/db/ora-02091/
-- The whole transaction is rolled back, so the capacity is kept to 60 seats.
SQL> select id_FLIGHT, aircraft_capacity from FLIGHTS where id_FLIGHT=92363;
ID_FLIGHT AIRCRAFT_CAPACITY
---------- -----------------
92363 60
Involving an additional control over BOOKINGS.
Let us make the example more complex by preventing a flight from reducing its capacity (for example, from 120 to 100 seats) if there are more reserved seats than the new aircraft provides.
In this way, the UPDATE with the seat change to a smaller aircraft would only be allowed if a number of seats greater than the new capacity has not been sold.
Therefore, I am going to create a second SQL Assertion to verify reservations. If there are more reservations than the current aircraft capacity, that UPDATE on the FLIGHTS table must fail.
CREATE ASSERTION capacity_not_under_booked_seats
CHECK (
NOT EXISTS (
SELECT 'seat out of capacity'
FROM FLIGHTS f, BOOKINGS b
WHERE b.FLIGHT_ID_FLIGHT = f.ID_FLIGHT
AND b.SEAT_ID_SEAT > f.AIRCRAFT_CAPACITY
)
);
Although here I am doing a small cheat.
The seats of an aircraft, in our data model, are ordinal data; that is, they are numbered from 1 to the number of seats on the aircraft. Reservations, if they are made sequentially, would also fill the aircraft in ordinal order: the first reservation occupies seat 1 of the flight, the second seat 2, and so on.
If customers choose the seat because they prefer to be at the back of the aircraft, for example, the last seats would be occupied, but perhaps the aircraft would be half empty.
Not to mention that, if there are cancellations, those seats should become unoccupied again and it should allow updating to a smaller aircraft.
SQL> CREATE ASSERTION capacity_not_under_booked_seats
2 CHECK (
3 NOT EXISTS (
4 SELECT 'seat out of capacity'
5 FROM FLIGHTS f, BOOKINGS b
6 WHERE b.FLIGHT_ID_FLIGHT = f.ID_FLIGHT
7 AND b.SEAT_ID_SEAT > f.AIRCRAFT_CAPACITY
8 )
9 );
Assertion created.
SQL> select flight_id_flight, count(*) from bookings group by flight_id_flight
2 having count(*)>13;
FLIGHT_ID_FLIGHT COUNT(*)
---------------- ----------
25967 14
6866 14
11709 14
SQL> select * from bookings where flight_id_flight=25967;
ID_BOOKING PRICE CUST_NIF BT_ AGENCY_ID_AGENCY SEAT_ID_SEAT FLIGHT_ID_FLIGHT
---------- ---------- ---------- --- ---------------- ------------ ----------------
UZgKVUiDRO 173.66 80302650-B ONL 11 3 25967
AGdCabZDT^ 199.3 67926445-P DIR 138 5 25967
SJ]RF\Ee_R 175.45 9883255-U DIR 116 50 25967
bSL]fgSFGa 178.03 18882008-H DIR 26 66 25967
]MJZbhS`RR 187.23 17329572-Q ONL 6 90 25967
[NhIFXHV[V 152.54 88344194-Q DIR 15 101 25967
TQBaNNGiJF 120.16 52876976-Q DIR 10 103 25967
WYHHhHZBQd 122.3 70987254-R DIR 106 115 25967
LRFVG^cYGX 110.17 15351714-X PHO 13 11 25967
dEibXIVADF 103.26 30447653-J DIR 122 37 25967
SLVLai^Keb 138 37962698-L PHO 114 87 25967
D[N_BEbWIX 138.16 8916597-A ONL 101 93 25967
EBDCWGYQaf 189.82 23285378-H ONL 57 97 25967
QALS`LCHDO 127.29 50311256-U ONL 139 107 25967
14 rows selected.
SQL> update flights set aircraft_capacity=100 where id_flight=25967;
update flights set aircraft_capacity=100 where id_flight=25967
*
ERROR at line 1:
ORA-08601: SQL assertion (FLIGHTS.CAPACITY_NOT_UNDER_BOOKED_SEATS) violated.
Help: https://docs.oracle.com/error-help/db/ora-08601/
In this case, then, we could not reduce the occupancy of flight 25967 from 120 to 100 seats because seats 101, 103, 107 and 115 are occupied.
The SQL Assertion that we have created, comparing the seat number with the capacity, is a somewhat “rough” measure. The optimal thing in this case would be to count the reserved seats in order to allow, or not, reducing the capacity of the flight to a smaller aircraft.
The SQL Assertion code could be the following:
CREATE ASSERTION capacity_not_under_booked_seats
CHECK (
NOT EXISTS (
SELECT 1
FROM FLIGHTS f
WHERE f.AIRCRAFT_CAPACITY < (
SELECT COUNT(*)
FROM BOOKINGS b
WHERE b.FLIGHT_IUD_FLIGHT = f.ID_FLIGHT
)
)
);
And I say “could” because this syntax is not allowed.
SQL Assertions come with a lot of limitations. We can only use them in SQL statements that do not contain group functions; they do not support ANSI SQL; they do not resolve accesses through synonyms; they are not allowed on materialized views or context information (SYSDATE, USERENV, CURRENT_SCHEMA, etc.).
They must be strictly deterministic.
In case you want to take a look at the SQL Assertions restrictions, here you have the official documentation with the full list of restrictions:
https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/create-assertion.html
And in case you think that, by means of analytic functions, perhaps you can get around the restriction on the use of group functions, you should know that they are also part of the list of non-allowed features, although the error “SYS owned tables are not supported” is not very illustrative.
SQL> CREATE ASSERTION capacity_not_under_booked_seats
2 CHECK (
3 NOT EXISTS (
4 SELECT 'seat out of capacity'
5 FROM FLIGHTS f, BOOKINGS b
6 WHERE b.FLIGHT_ID_FLIGHT = f.ID_FLIGHT
7 QUALIFY ROW_NUMBER() OVER (
8 PARTITION BY b.FLIGHT_ID_FLIGHT order by b.SEAT_ID_SEAT
9 ) > f.AIRCRAFT_CAPACITY
10 )
11 );
FROM FLIGHTS f, BOOKINGS b
*
ERROR at line 5:
ORA-08689: CREATE ASSERTION failed
ORA-08697: SYS owned tables are not supported.
Help: https://docs.oracle.com/error-help/db/ora-08689/
Another example: checking that a customer does not take two flights on the same day.
Let us explore another example: validating that a customer does not have reservations on different flights for the same day. In this case, the flight date data is in the FLIGHTS table, and for that we need to join flights and reservations twice.
Remember that we cannot use either group or analytic functions.
CREATE ASSERTION only_one_flight_per_day
CHECK (
NOT EXISTS (
SELECT 1
FROM bookings b1,
bookings b2,
flights f1,
flights f2
WHERE b1.cust_nif = b2.cust_nif
AND b1.rowid < b2.rowid
AND b1.flight_id_flight = f1.id_flight
AND b2.flight_id_flight = f2.id_flight
AND TRUNC(f1.flight_date) = TRUNC(f2.flight_date)
)
) ENABLE NOVALIDATE;
Here we run into more restrictions in the validation of the constraint. Given that in the RESERVAS table the value of CLI_NIF can be NULL, the SQL Assertion will fail with an error warning us that the checking of the assertion must be of type FAST, and that under the usual conditions, it is not possible to guarantee it.
We will have two options for it to work: either we add the clauses WHERE CLI_NIF IS NOT NULL to the SQL Assertion code, or we will modify the RESERVAS table to prevent CLI_NIF from being NULL
SQL> CREATE ASSERTION only_one_flight_per_day
2 CHECK (
3 NOT EXISTS (
4 SELECT 1
5 FROM bookings b1,
6 bookings b2,
7 flights f1,
8 flights f2
9 WHERE b1.cust_nif = b2.cust_nif
10 AND b1.rowid < b2.rowid
11 AND b1.flight_id_flight = f1.id_flight
12 AND b2.flight_id_flight = f2.id_flight
13 AND TRUNC(f1.flight_date) = TRUNC(f2.flight_date)
14 )
15 ) ENABLE NOVALIDATE;
FROM bookings b1,
* ERROR at line 5: ORA-08689: CREATE ASSERTION failed ORA-08673: Equijoin "B1"."CUST_NIF"="B2"."CUST_NIF"
found does not meet the criteria to do a FAST validation.
Help: https://docs.oracle.com/error-help/db/ora-08689/
SQL> alter table bookings modify cust_nif not null;
Table altered.
SQL> CREATE ASSERTION only_one_flight_per_day
2 CHECK (
3 NOT EXISTS (
4 SELECT 1
5 FROM bookings b1,
6 bookings b2,
7 flights f1,
8 flights f2
9 WHERE b1.cust_nif = b2.cust_nif
10 AND b1.rowid < b2.rowid
11 AND b1.flight_id_flight = f1.id_flight
12 AND b2.flight_id_flight = f2.id_flight
13 AND TRUNC(f1.flight_date) = TRUNC(f2.flight_date)
14 )
15 ) ENABLE NOVALIDATE;
Assertion created.
SQL> insert into bookings values ('UESWeNMB\]',156.14,'10339188-Y','DIR',3,38,42795);
insert into bookings values ('UESWeNMB\]',156.14,'10339188-Y','DIR',3,38,42795)
*
ERROR at line 1:
ORA-08601: SQL assertion (FLIGHTS.ONLY_ONE_FLIGHT_PER_DAY) violated.
Help: https://docs.oracle.com/error-help/db/ora-08601/
One precaution here: we must be careful with performance.
The previous SQL Assertion has been created with the ENABLE NOVALIDATE clause instantly. Although the checks may not seem to affect performance when executing the SQL code that validates it, the creation of these SQL Assertions can take a considerable amount of time due to the validation of all existing cases.
We can see that the query executes in less than one second, but enabling the Assertion with validation takes more than 1 hour.
SQL> SELECT 1 2 FROM bookings b1, 3 bookings b2, 4 flights f1, 5 flights f2 6 WHERE b1.cust_nif = b2.cust_nif 7 AND b1.rowid < b2.rowid 8 AND b1.flight_id_flight = f1.id_flight 9 AND b2.flight_id_flight = f2.id_flight 10 AND TRUNC(f1.flight_date) = TRUNC(f2.flight_date); no rows selected Elapsed: 00:00:00.14 SQL> alter assertion ONLY_ONE_FLIGHT_PER_DAY enable validate; Elapsed: 01:40:03.23
What is underneath?
Unlike constraints, here we have auxiliary objects and segments that we cannot lose sight of. Some of them can become considerable in size compared to the tables they refer to, although for that we will have to analyze the volume of the segments.
Let us take a look
SQL> select table_name, blocks*(8*1024) bytes from user_tables;
TABLE_NAME BYTES
----------------------------------------------------- ----------
CUSTOMERS 778240
AIRLINES 40960
AGENCIES 40960
FLIGHT_TYPES 40960
TRAVEL_AGENCIES 40960
GENDERS 40960
SEATS 82714624
AIRPORTS 40960
EMPLOYMENT_STATUS 40960
COUNTRIES 40960
BOOKINGS 7159808
FLIGHTS 4063232
MARITAL_STATUS 40960
CATERING 40960
BOOKING_TYPES 40960
AGE_GROUPS 40960
CITIES 40960
ORA$SA$TE_FLIGHTS
ORA$SA$TE_SEATS
ORA$SA$VC_CAPACITY_CONSISTENT_WITH_SEATS
ORA$SA$TE_BOOKINGS
ORA$SA$VC_CAPACITY_NOT_UNDER_BOOKED_SEATS
ORA$SA$VC_ONLY_ONE_FLIGHT_PER_DAY
BOOKINGS_BACKUP 65536
24 rows selected.
SQL> select * from ORA$SA$TE_FLIGHTS;
select * from ORA$SA$TE_FLIGHTS
*
ERROR at line 1:
ORA-08709: Reads from SQL assertion auxiliary tables are restricted.
Help: https://docs.oracle.com/error-help/db/ora-08709/
To query the dictionary, we have the views USER_ASSERTIONS, USER_ASSERTION_DEPENDENCIES and USER_ASSERTION_LOCK_MATRIX at our disposal
SQL> desc USER_ASSERTIONS Name Null? Type ----------------------------------------- -------- ---------------------------- ASSERTION_NAME VARCHAR2(128) OBJECT_ID NOT NULL NUMBER STATUS VARCHAR2(8) DEFERRABLE VARCHAR2(14) DEFERRED VARCHAR2(9) VALIDATED VARCHAR2(13) INVALID VARCHAR2(7) DEFINITION_SQL CLOB SQL> desc USER_ASSERTION_DEPENDENCIES Name Null? Type ----------------------------------------- -------- ---------------------------- ASSERTION_NAME VARCHAR2(128) REFERENCED_OWNER VARCHAR2(128) REFERENCED_NAME VARCHAR2(128) REFERENCED_TYPE VARCHAR2(23) REFERENCED_ID NOT NULL NUMBER VALIDATION_TYPE VARCHAR2(8) VALIDATION_EVENT VARCHAR2(24) SQL> desc USER_ASSERTION_LOCK_MATRIX Name Null? Type ----------------------------------------- -------- ---------------------------- ASSERTION_NAME VARCHAR2(128) TABLE_ID NUMBER TABLE_OWNER VARCHAR2(128) TABLE_NAME VARCHAR2(128) ADJ_TABLE_ID NUMBER ADJ_TABLE_OWNER VARCHAR2(128) ADJ_TABLE_NAME VARCHAR2(128) LOCK_SCOPE VARCHAR2(18) JOIN_COLUMNS VARCHAR2(4000) LOCK_MODE VARCHAR2(8) LOCK_TEXT VARCHAR2(4000) SQL> select assertion_name, object_id, status, validated from user_assertions; ASSERTION_NAME OBJECT_ID STATUS VALIDATED ---------------------------------------- ---------- -------- ------------- CAPACITY_CONSISTENT_WITH_SEATS 131482 ENABLED VALIDATED ONLY_ONE_FLIGHT_PER_DAY 131501 ENABLED NOT VALIDATED CAPACITY_NOT_UNDER_BOOKED_SEATS 131492 ENABLED VALIDATED Elapsed: 00:00:00.01 SQL> select assertion_name, referenced_owner, referenced_name, validation_type, validation_event 2 from user_assertion_dependencies; ASSERTION_NAME REFERENCED REFERENCED VALIDATI VALIDATION_EVENT ---------------------------------------- ---------- ---------- -------- ------------------------ CAPACITY_CONSISTENT_WITH_SEATS FLIGHTS SEATS FAST ROWS INSERTED OR UPDATED ONLY_ONE_FLIGHT_PER_DAY FLIGHTS BOOKINGS FAST ROWS INSERTED OR UPDATED CAPACITY_NOT_UNDER_BOOKED_SEATS FLIGHTS BOOKINGS FAST ROWS INSERTED OR UPDATED ONLY_ONE_FLIGHT_PER_DAY FLIGHTS BOOKINGS FAST ROWS INSERTED OR UPDATED CAPACITY_CONSISTENT_WITH_SEATS FLIGHTS FLIGHTS FAST ROWS INSERTED OR UPDATED CAPACITY_NOT_UNDER_BOOKED_SEATS FLIGHTS FLIGHTS FAST ROWS INSERTED OR UPDATED ONLY_ONE_FLIGHT_PER_DAY FLIGHTS FLIGHTS FAST ROWS INSERTED OR UPDATED ONLY_ONE_FLIGHT_PER_DAY FLIGHTS FLIGHTS FAST ROWS INSERTED OR UPDATED 8 rows selected. SQL> select assertion_name, table_name, adj_table_name, lock_scope, join_columns, lock_mode, lock_text 2 from user_assertion_lock_matrix 3 order by assertion_name; ASSERTION_NAME TABLE_NAME ADJ_TABLE_ LOCK_SCOPE JOIN_COLUMNS LOCK_MOD LOCK_TEXT ---------------------------------------- ---------- ---------- ------------------ ----------------- -------- --------------- CAPACITY_CONSISTENT_WITH_SEATS SEATS SEATS JOIN_COLUMN_VALUES FLIGHT_ID_FLIGHT SX T#:0 C#:1:3 CAPACITY_CONSISTENT_WITH_SEATS SEATS FLIGHTS JOIN_COLUMN_VALUES SEAT_ID S T#:1 C#:1:1 CAPACITY_CONSISTENT_WITH_SEATS FLIGHTS FLIGHTS JOIN_COLUMN_VALUES ID_FLIGHT SX T#:1 C#:1:1 CAPACITY_CONSISTENT_WITH_SEATS FLIGHTS SEATS JOIN_COLUMN_VALUES DETAILS S T#:0 C#:1:3 CAPACITY_NOT_UNDER_BOOKED_SEATS FLIGHTS BOOKINGS JOIN_COLUMN_VALUES CAT_ID_CATERING S T#:0 C#:1:7 CAPACITY_NOT_UNDER_BOOKED_SEATS BOOKINGS BOOKINGS JOIN_COLUMN_VALUES FLIGHT_ID_FLIGHT SX T#:0 C#:1:7 CAPACITY_NOT_UNDER_BOOKED_SEATS BOOKINGS FLIGHTS JOIN_COLUMN_VALUES ID_BOOKING S T#:1 C#:1:1 CAPACITY_NOT_UNDER_BOOKED_SEATS FLIGHTS FLIGHTS JOIN_COLUMN_VALUES ID_FLIGHT SX T#:1 C#:1:1 ONLY_ONE_FLIGHT_PER_DAY FLIGHTS BOOKINGS JOIN_COLUMN_VALUES CAT_ID_CATERING S T#:0 C#:1:7 ONLY_ONE_FLIGHT_PER_DAY FLIGHTS BOOKINGS TABLE S T#:0 C#:0: ONLY_ONE_FLIGHT_PER_DAY FLIGHTS FLIGHTS TABLE SX T#:3 C#:0: ONLY_ONE_FLIGHT_PER_DAY FLIGHTS BOOKINGS JOIN_COLUMN_VALUES CAT_ID_CATERING S T#:1 C#:1:7 ONLY_ONE_FLIGHT_PER_DAY BOOKINGS FLIGHTS JOIN_COLUMN_VALUES ID_BOOKING S T#:2 C#:1:1 ONLY_ONE_FLIGHT_PER_DAY BOOKINGS FLIGHTS TABLE S T#:2 C#:0: ONLY_ONE_FLIGHT_PER_DAY BOOKINGS FLIGHTS TABLE S T#:3 C#:0: ONLY_ONE_FLIGHT_PER_DAY BOOKINGS FLIGHTS JOIN_COLUMN_VALUES ID_BOOKING S T#:3 C#:1:1 ONLY_ONE_FLIGHT_PER_DAY FLIGHTS FLIGHTS TABLE SX T#:2 C#:0: ONLY_ONE_FLIGHT_PER_DAY FLIGHTS FLIGHTS JOIN_COLUMN_VALUES ID_FLIGHT SX T#:2 C#:1:1 ONLY_ONE_FLIGHT_PER_DAY FLIGHTS FLIGHTS TABLE S T#:2 C#:0: ONLY_ONE_FLIGHT_PER_DAY FLIGHTS FLIGHTS TABLE S T#:3 C#:0: ONLY_ONE_FLIGHT_PER_DAY FLIGHTS FLIGHTS JOIN_COLUMN_VALUES ID_FLIGHT SX T#:3 C#:1:1 ONLY_ONE_FLIGHT_PER_DAY BOOKINGS BOOKINGS JOIN_COLUMN_VALUES CUST_NIF SX T#:1 C#:1:3 ONLY_ONE_FLIGHT_PER_DAY BOOKINGS BOOKINGS TABLE SX T#:1 C#:0: ONLY_ONE_FLIGHT_PER_DAY BOOKINGS BOOKINGS JOIN_COLUMN_VALUES FLIGHT_ID_FLIGHT SX T#:1 C#:1:7 ONLY_ONE_FLIGHT_PER_DAY BOOKINGS BOOKINGS JOIN_COLUMN_VALUES CUST_NIF S T#:1 C#:1:3 ONLY_ONE_FLIGHT_PER_DAY BOOKINGS BOOKINGS JOIN_COLUMN_VALUES CUST_NIF S T#:0 C#:1:3 ONLY_ONE_FLIGHT_PER_DAY BOOKINGS BOOKINGS JOIN_COLUMN_VALUES CUST_NIF SX T#:0 C#:1:3 ONLY_ONE_FLIGHT_PER_DAY BOOKINGS BOOKINGS JOIN_COLUMN_VALUES FLIGHT_ID_FLIGHT SX T#:0 C#:1:7 ONLY_ONE_FLIGHT_PER_DAY BOOKINGS BOOKINGS TABLE SX T#:0 C#:0: ONLY_ONE_FLIGHT_PER_DAY FLIGHTS BOOKINGS TABLE S T#:1 C#:0: 30 rows selected.
Available only in Oracle AI Database starting with release 23.26.1
We are facing a completely new feature. Available only starting with the January 2026 RU for Oracle AI Database (26ai).
Not only that: this is a new feature in the world of databases in general, since until now it only existed in the theoretical framework.
The behavior and syntax are very reminiscent of constraint management, which allows combinations between ENABLE/DISABLE and VALIDATE/NOVALIDATE. I do not know whether in the future the list of usage restrictions will be reduced, nor whether we will be able to have SQL Assertions with group expressions or analytic functions, but the idea of getting ever closer to the standard and being able to implement from the engine the greatest possible number of business rules over the data seems super interesting to me.
I miss an EXCEPTIONS INTO clause to manage exceptions in an EXCEPTIONS table, just as happens with constraints, and we also do not have documentation on whether the optimizer will use the logic of the assertions to improve cardinality and selectivity estimates.
In any case, we finally see implemented a feature designed to improve business logic that has taken decades to come to light.
Just like this article.