Assertions in oracle AI Database 26ai

A Brief Primer: Database Constraints

Database constraints enable developers to enforce business rules and data integrity right where the data lives, the tables! They help do things like ensure data uniqueness in a table, prevent incorrect data from being keyed in, prevent null values and enforce relationships between tables to name a few (I go into a lot more detail in a separate post about database constraints). Traditional constraints (Primary key, Unique, Not Null, Check) can be defined on a single table. The check constraint can enforce some logic on a column such as allowing only specific values, or check that a columns value is less or greater than another. However, none of the traditional constraints can enforce more complex business rules.

Introduction

In Oracle AI Database 26ai (23.26.1.0.0 and higher), the Assertions feature goes further and can be used to check for conditions across tables unlike traditional constraints. They are schema-level objects which ensure that data conforms to a rule. After DML operations, Oracle validates that the assertion remains true. Previously, developers have had to use features like triggers to achieve the same or similar effects.

Required Privileges

In order to define an assertion:

• The CREATE ASSERTION system privilege is required. This implicitly allows the user to execute DROP ASSERTION and ALTER ASSERTION on assertions in its schema.
• To create an assertion affecting a table in a different schema, the ASSERTION REFERENCES privilege on the table is required.
• To create an assertion on any table  in any schema except SYS or AUDSYS, the CREATE ANY ASSERTION system privilege is required.

It is worthy of note that assertion privileges are very powerful and could be misused. A user could create an assertion that prevents the insertion of any data into a table for example.

Limitations

• Assertions must be deterministic. They cannot make use of sysdate, userenv, sys_context, PL/SQL Functions.
• A mix of nested NOT EXISTS and EXISTS can be up to three levels deep.
• As of this writing, there is no support for aggregate functions, ANSI joins and set operators.

It is also worth noting that enforcing assertions introduce additional validation work during DML and therefore incur some runtime overhead.

Assertion Expression types

Assertions are essentially Boolean expressions that return false (causing the assertion to fail and raise an error) or true (meaning the assertion passed and the transaction proceeds successfully) we can use two types of expressions to create an assertion they can be existential or universal.

Existential expression

This type of expression makes use of the [NOT] EXISTS syntax. Existential expressions evaluate the database state by verifying the presence or absence of a violating dataset. This approach is rooted in negation. The database evaluates a subquery which can be nested up to three levels deep and blocks the parent DML transaction if the invalid data pattern is found.

Universal expression

This type of expression makes use of a new ALL…SATISFY clause and helps to reduce the number of negations when specifying assertions. Instead of seeking out violations to a rule, it uses a two-step validation model. First, the ALL clause isolates a target dataset. Second, the SATISFY clause evaluates a Boolean expression against every single row returned. If the condition holds true for all rows (or if no rows are returned), the transaction proceeds.

Let’s see how assertions work using 2 examples; one for each type of expression.

Examples

Setup

The code below creates the tables and inserts the data needed for the examples in this post

DROP ASSERTION IF EXISTS check_it_salary ;
DROP ASSERTION IF EXISTS check_dept_salary_cap ;

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;

CREATE TABLE dept
  ( dept_id   NUMBER PRIMARY KEY
   ,dept_name VARCHAR2(32 CHAR) 
   ,dept_max_sal   NUMBER 
 );

CREATE TABLE emp
 ( emp_id   NUMBER PRIMARY KEY
  ,emp_name VARCHAR2(64 CHAR)
  ,salary   NUMBER  NOT NULL
  ,dept_id  NUMBER REFERENCES dept(dept_id) NOT NULL
 );

INSERT INTO dept 
  VALUES  (1,'IT', 10000)
         ,(2,'HR', 30000)
         ,(3,'SALES', 15000)
         ,(4,'FINANCE', 12000) 
         ,(5,'ANALYSTS', 22000) ;

INSERT INTO emp (emp_id, emp_name, salary, dept_id)
VALUES
       ( 1, 'William Smith'  , 7400 , 4)
      ,( 2, 'Elizabeth Bates', 7300 , 1)
      ,( 3, 'Sundita Kumar'  , 6100 , 4)
      ,( 4, 'Ellen Abel'     , 11000, 2)
      ,( 5, 'Alyssa Hutton'  , 8800 , 1)
      ,( 6, 'Jonathon Taylor', 8600 , 3)
      ,( 7, 'Jack Livingston', 8400 , 3)
      ,( 8, 'Kimberely Grant', 7000 , 1)
      ,( 9, 'Charles Johnson', 6200 , 4)
      ,(10, 'Winston Taylor' , 3200 , 2)
;

COMMIT;

Business Requirements

Imagine we get the following requests from the business :

• Requirement #1 : To make sure no employee in the IT department makes less than 5000 or more than 10,000.
• Requirement #2: To make sure that no employee in any department makes more than the max salary for that department.

Requirement #1

We can create an assertion to handle this requirement using an existential expression.

CREATE ASSERTION check_it_salary CHECK (
 NOT EXISTS (
     SELECT 'x'
     FROM emp
     WHERE dept_id = 1
     AND (salary < 5000 OR salary > 10000)
       )
 );

Assertion created.

If we then try to update an IT employee’s salary to 12000, we get an error saying the assertion was violated.

UPDATE emp
SET salary = 12000
WHERE dept_id = 1 ;

Error report -
SQL Error: ORA-08601: SQL assertion (HR.CHECK_IT_SALARY) violated.
Help: https://docs.oracle.com/error-help/db/ora-08601/

If we also try to insert a new row for an employee in the IT department (dept id 1) with a salary greater than 10000, the insert fails as it violates the assertion.

INSERT INTO emp
VALUES (50, 'John Doe', 500000, 1);

Error at Command Line : 1 Column : 13
Error report -
SQL Error: ORA-08601: SQL assertion (HR.CHECK_IT_SALARY) violated.
Help: https://docs.oracle.com/error-help/db/ora-08601/

Requirement #2

We can create an assertion to handle this requirement using a universal expression.

CREATE ASSERTION check_dept_salary_cap CHECK (
  ALL (
       SELECT *
       FROM   emp
      ) e
        SATISFY (
         NOT EXISTS (
                      SELECT 'x'
                      FROM dept d
                      WHERE e.dept_id = d.dept_id
                      AND  e.salary > d.dept_max_sal
                    )
                 )
      ) ;

Assertion created.

If we attempt to insert a new row for an employee with salary greater than 10,000 (the max salary for that department) in IT we get an error.

INSERT INTO emp
VALUES (11, 'Winnie the pooh', 13000, 1) ;

INSERT INTO emp
            *
Error at Command Line : 1 Column : 13
Error report -
SQL Error: ORA-08601: SQL assertion (HR.CHECK_DEPT_SALARY_CAP) violated.
Help: https://docs.oracle.com/error-help/db/ora-08601/

Notice that assertions, unlike traditional constraints, are standalone schema objects rather than table objects. An assertion is its own object and in fact in this scenario references 2 different tables. While this assertion exists, the referenced tables cannot be dropped.

Also, note that the NOT NULL constraint on the dept_id column in the employee table is crucial in order for this assertion to be defined and the data types between emp.dept_id and dept.dept_id have to match. Without these, you will get the following error:

ERROR at line 6:
ORA-08689: CREATE ASSERTION failed
ORA-08673: Equijoin "EMP"."DEPT_ID"="D"."DEPT_ID" found does not meet the
criteria to do a FAST validation.
Help: https://docs.oracle.com/error-help/db/ora-08689/

Assertion Metadata

We can use various views to see the metadata about assertions, including:

• [CDB|DBA|ALL|USER]_assertions
• [CDB|DBA|ALL|USER]_assertion_dependencies
• [CDB|DBA|ALL|USER]_assertion_lock_matrix

Assertion states

Similar to constraints, assertions also have a state (See here) . Their states can be changed using the ALTER ASSERTION command.

Main Takeaways About Assertions

• Assertions are generic, schema-level, multi-row, and multi-table constraints which ensure that data conforms to a rule.
• They can restrict any valid condition across multiple rows or tables.
• They can reference multiple tables as well as tables in another schema.
• They are validated only when a DML operation could have potentially violated the assertion.
• They should not be used in scenarios where traditional constraints would work just fine.

Conclusion

Arguably one of the greatest addition to any modern relational database, part of the SQL standard since 1992, unimplemented by any major database engine until now with Oracle; Assertions provide us with centralized, robust data integrity right at the home of our data: The database. We can now declare complex, multi-table business rules directly inside the database schema; ensuring the rule is strictly enforced across the entire system regardless of how data enters the database. Instead of having to write, maintain and debug complex triggers and/or application code, we can now enforce business rules with elegant, declarative SQL. This is a massive win for system security, code cleanliness, and absolute data integrity.

Learn more

To learn more about assertions in depth, see the video by Toon Koppelaars and Chris Saxon.

Also check out these blogs

• How to define cross-table constraints with assertions in Oracle AI Database
• Guarantee at least one in one-to-many relationships in Oracle AI Database

cheers , Harris🎊

_{Updated 9th August 2026.}