How soon will time cover all things, and how many it has covered already.

Marcus Aurelius

Intro

Let’s start by looking at the theoretical reason SQL assertions exist and why during history real systems like Oracle Database moved away from them. This is an important concept in the history of relational database design.

The Original Idea in the Relational Model

The relational model was introduced by Edgar F. Codd in 1970.

In the relational theory:

• the database state must always satisfy logical constraints
• constraints should be declarative, not procedural

This means the database system itself guarantees correctness.

Assertions were meant to express general integrity rules.

Example idea

“The total salary of a department must not exceed its budget.” Instead of writing triggers or application logic, the designer would simply declare:

CREATE ASSERTION dept_budget_rule
CHECK ( ... );
-- The database would automatically enforce it.

Types of Constraints in Relational Theory

Relational theory defined several categories:

Domain constraints

Restrictions on attribute values. Example:

salary > 0

Key constraints

Ensure entity uniqueness. Example:

PRIMARY KEY

Referential integrity

Relationships between tables. Example:

FOREIGN KEY

Assertions (general constraints)

These cover all other business rules.

Examples:

• total department salary ≤ department budget
• every project must have at least one employee
• manager salary > employee salary

Assertions were supposed to express any logical rule about the database.

Why Vendors Avoided Assertions

What is a SQL Assertion?

In SQL, an assertion is a database constraint that ensures a condition is always true across one or more tables. It is defined at the database level, not tied to a single table.

Conceptually, an assertion says:

“The database must never reach a state where this condition is false.”

Assertions are part of the SQL standard (ISO SQL) and are defined using the CREATE ASSERTION statement. Feature F521 – “Assertions” is an optional feature in the SQL standard (defined in standards such as ISO/IEC 9075 SQL Standard). 

Example (standard SQL):

CREATE ASSERTION salary_limit
CHECK (
   NOT EXISTS (
      SELECT *
      FROM employees
      WHERE salary > 200000
   )
);

Meaning:
No employee can have a salary greater than 200000.

Assertions are useful for global integrity rules, such as:

• A department’s total salary must not exceed its budget.
• A bank account balance must never become negative.
• The number of managers must be less than the number of employees.

The Problem with Assertions

Assertions are powerful but expensive to enforce because:

• They may involve multiple tables
• The database must check them after every update, insert, or delete
• This could require full scans or complex queries

Because of this cost, most commercial databases never implemented them.

When commercial databases like those from Oracle Corporation appeared, engineers realised assertions caused serious implementation challenges:

1. Performance
   • A global assertion might require scanning:
     • multiple tables
     • millions of rows
     • every time data changes
   • This can make transactions extremely slow.
2. Incremental Checking. Efficient enforcement requires incremental updates.
   • Example: If one employee salary changes, the system must update the department total without recalculating everything. That requires sophisticated algorithms.
3. Concurrency Control
   • Modern databases allow many simultaneous transactions.
   • Assertions can create conflicts between transactions, forcing:
     • extra locks
     • transaction rollbacks
     • complicated validation logic

Practical Solution Used by Real Systems

Instead of assertions, most modern databases implement simpler constraint types:

For complex rules, developers and clients use a solution developed by using one or several of:

• triggers
• constraints ,CHECK constraints and deferrable constraints 
• stored procedures
• application logic

Example:

ALTER TABLE employees
ADD CONSTRAINT salary_check
CHECK (salary <= 200000);

But this works only within one row of one table.

Why Simpler Constraints Work Well

Simple constraints are local:

• they apply to a row or table
• they can be validated quickly
• they integrate well with indexes

Example:

CHECK (salary > 0)

This can be validated instantly during insert or update.

Modern Database Philosophy

Modern database systems focus on:

• fast transactions
• high concurrency
• scalability

Therefore, they favor:

• simple declarative constraints
• procedural logic for complex rules

Even modern systems like:

• PostgreSQL
• MySQL
• Microsoft SQL Server

do not fully implement SQL assertions.

Summary

 Why SQL Assertions Exist in the Standard but Not always in Real Databases

It would nicely connect:

• relational theory
• SQL standards
• real database engineering.

Why SQL Assertions Are Rarely Implemented?

Let’s go even deeper into why assertions are almost never implemented in real databases and how systems like Oracle Database work around them. This is an interesting topic for database architecture and advanced SQL design.

The SQL standard includes the statement:

CREATE ASSERTION

but almost no major database system (Oracle, PostgreSQL, MySQL, SQL Server) historically supported it.

The reason is performance and complexity.

Assertions Require Global Consistency Checking

Unlike normal constraints, assertions may reference multiple tables and multiple rows.

Example:

CREATE ASSERTION dept_budget
CHECK (
   NOT EXISTS (
      SELECT d.dept_id
      FROM departments d, employees e
      WHERE d.dept_id = e.dept_id
      GROUP BY d.dept_id, d.budget
      HAVING SUM(e.salary) > d.budget
   )
);

This rule says:

The total salary of employees in a department must not exceed its budget.

If assertions were supported, the database would have to check this rule whenever any of these happen:

• employee inserted
• employee salary updated
• employee moved to another department
• department budget updated
• employee deleted

Potentially, many tables and rows are affected.

Query Optimizer Challenges

To enforce assertions efficiently, the optimizer must know:

• which tables affect the assertion
• which rows must be rechecked
• how to avoid full-table scans

This creates difficult questions:

• When do we check the assertion?
• After every statement?
• At transaction commit?
• Incrementally?

For complex assertions this becomes very expensive.

Transaction Isolation Problems

Assertions interact badly with concurrent transactions.

Example scenario:

Transaction A:

INSERT employee salary 50000 ...

Transaction B:

INSERT employee salary 60000 ...

Individually both satisfy the constraint.

But together they violate it.

The database would need:

• special locking
• or deferred checking
• or transaction coordination

This complicates concurrency control.

Implementation Cost vs Demand

Vendors like Oracle Corporation probably chose not to implement assertions because:

• few real applications require them
• triggers can simulate them
• performance risks are high

So vendors prioritized:

• indexes
• query optimization
• partitioning
• parallel execution

instead of global assertions.

How Oracle Replaced Assertions

In Oracle Database, the typical solutions are:

Constraints (fast, built-in)

• PRIMARY KEY
• UNIQUE
• FOREIGN KEY
• CHECK

These are row-level or table-level rules.

Triggers (most common substitute)

Triggers enforce complex logic.

For multi-table constraints, Oracle uses triggers.

Example 1:

CREATE OR REPLACE TRIGGER dept_salary_limit
AFTER INSERT OR UPDATE ON employees
FOR EACH ROW
DECLARE
    total_salary NUMBER;
BEGIN
    SELECT SUM(salary)
    INTO total_salary
    FROM employees
    WHERE department_id = :NEW.department_id;
    IF total_salary > 1000000 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Department salary limit exceeded');
    END IF;
END;

This simulates an assertion-like rule.

Example 2:

CREATE OR REPLACE TRIGGER check_budget
AFTER INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
   -- check salary budget rule
END;

Example 3 Trigger-Based Assertion (Most Common used method)

Create a trigger that validates the rule during inserts or updates.

sample code:

CREATE OR REPLACE TRIGGER check_department_budget
AFTER INSERT OR UPDATE ON employees
FOR EACH ROW
DECLARE
   total_salary NUMBER;
   dept_budget NUMBER;
BEGIN
   SELECT SUM(salary)
   INTO total_salary
   FROM employees
   WHERE dept_id = :NEW.dept_id;
   SELECT budget
   INTO dept_budget
   FROM departments
   WHERE dept_id = :NEW.dept_id;
   IF total_salary > dept_budget THEN
      RAISE_APPLICATION_ERROR(
         -20001,
         'Department salary exceeds budget'
      );
   END IF;
END;
CREATE OR REPLACE TRIGGER check_department_budget
AFTER INSERT OR UPDATE ON employees
FOR EACH ROW
DECLARE
   total_salary NUMBER;
   dept_budget NUMBER;
BEGIN
   SELECT SUM(salary)
   INTO total_salary
   FROM employees
   WHERE dept_id = :NEW.dept_id;
   SELECT budget
   INTO dept_budget
   FROM departments
   WHERE dept_id = :NEW.dept_id;
   IF total_salary > dept_budget THEN
      RAISE_APPLICATION_ERROR(
         -20001,
         'Department salary exceeds budget'
      );
   END IF;
END;
/

✔ Works like an assertion

✔ Prevents invalid transactions

Downside:

Harder to maintain

Must be written manually

Triggers allow:

• multi-table checks
• procedural logic
• custom error messages

Materialized Views

Sometimes systems use:

• summary tables
• materialized views
• constraint on aggregated data

Materialized View Constraint

Another enterprise technique.

Create a materialised view that detects violations.

Example:

CREATE MATERIALIZED VIEW dept_salary_violation
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT d.dept_id
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.budget
HAVING SUM(e.salary) > d.budget;

Then enforce:

ALTER MATERIALIZED VIEW dept_salary_violation
ADD CONSTRAINT no_salary_violation
CHECK (dept_id IS NULL);

If rows appear → constraint fails.

Advantages:

• Declarative
• Centralized rule

Used in large Oracle financial systems.

Materialized Views + Constraints

Another advanced method:

1. Create a materialized view that detects violations
2. Add a constraint preventing rows

Example idea:

CREATE MATERIALIZED VIEW salary_violation
AS
SELECT department_id
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 1000000;

Then enforce that it must remain empty.

This is useful in data warehouses.

Deferrable Constraints

Oracle also allows deferred checking:

DEFERRABLE INITIALLY DEFERRED

Meaning:

• constraint checked at transaction commit
• useful for complex consistency rules

Example 3 — Constraint with Function

Another workaround uses a deterministic function.

Example:

CREATE OR REPLACE FUNCTION check_budget(p_dept NUMBER)
RETURN NUMBER
IS
   total_salary NUMBER;
   dept_budget NUMBER;
BEGIN
   SELECT SUM(salary) INTO total_salary
   FROM employees
   WHERE dept_id = p_dept;
   SELECT budget INTO dept_budget
   FROM departments
   WHERE dept_id = p_dept;
   IF total_salary <= dept_budget THEN
      RETURN 1;
   ELSE
      RETURN 0;
   END IF;
END;

Constraint:

ALTER TABLE employees
ADD CONSTRAINT budget_check
CHECK (check_budget(dept_id) = 1);
...

Modern Perspective

Interestingly, modern database research is revisiting similar ideas through:

• declarative constraints
• incremental view maintenance
• constraint solving in distributed databases

But classic SQL assertions remained mostly theoretical.

Short summary

Key takeaway

Assertions are part of the SQL standard, but databases like Oracle Database implemented their functionality using constraints, triggers, and sometimes materialised views.

Key Insight

Assertions represent a pure relational ideal.

Real systems compromise because of:

• performance
• concurrency
• implementation complexity

This is a classic example of the gap between database theory and database engineering.

Oracle concepts that appear when trying to simulate assertions

Next, let’s look at important Oracle concepts that appear when trying to simulate assertions with triggers:

What is the Mutating Table Problem?

This is a classic topic in advanced Oracle design.

A mutating table error occurs when a trigger tries to read from or modify the same table that is currently being changed.

Oracle prevents this because the table is in an inconsistent intermediate state during the DML operation.

ORA-04091: table is mutating, trigger/function may not see it

Example Scenario

Suppose you try to enforce a rule:

Total salary of employees in a department must not exceed a limit.

Table:

CREATE TABLE employees (
   emp_id NUMBER,
   dept_id NUMBER,
   salary NUMBER
);

Now, you write a trigger:

CREATE OR REPLACE TRIGGER check_salary
AFTER INSERT OR UPDATE ON employees
FOR EACH ROW
DECLARE
   total_salary NUMBER;
BEGIN
   SELECT SUM(salary)
   INTO total_salary
   FROM employees
   WHERE dept_id = :NEW.dept_id;
   IF total_salary > 1000000 THEN
      RAISE_APPLICATION_ERROR(-20001,'Department salary exceeded');
   END IF;
END;

When this trigger runs, Oracle may raise:

ORA-04091: table EMPLOYEES is mutating

because the trigger is reading the table while it is being modified.

Why Oracle Prevents This

During an insert/update:

1. rows are changing
2. indexes may not yet be updated
3. constraints may not yet be validated

Allowing queries could produce inconsistent results.

To guarantee transaction correctness, Oracle blocks this.

Solutions Oracle already Provides

Statement-Level Trigger

Instead of checking per row, check after the entire statement finishes.

Example:

CREATE OR REPLACE TRIGGER check_salary
AFTER INSERT OR UPDATE ON employees
BEGIN
   -- run validation query here
END;

But you lose access to :NEW row values easily.

Package Variables

You can store changed rows in a PL/SQL package collection and check them later.

This is a traditional workaround.

Compound Triggers (Best Modern Solution)

Since Oracle 11g, Oracle introduced compound triggers.

They allow logic at multiple phases:

• BEFORE STATEMENT
• BEFORE EACH ROW
• AFTER EACH ROW
• AFTER STATEMENT

Example structure:

CREATE OR REPLACE TRIGGER salary_trigger
FOR INSERT OR UPDATE ON employees
COMPOUND TRIGGER
   TYPE dept_list IS TABLE OF NUMBER;
   changed_depts dept_list := dept_list();
BEFORE EACH ROW IS
BEGIN
   changed_depts.EXTEND;
   changed_depts(changed_depts.LAST) := :NEW.dept_id;
END BEFORE EACH ROW;
AFTER STATEMENT IS
BEGIN
   -- check salary totals here safely
END AFTER STATEMENT;
END;

This avoids the mutating table error.

Why This Matters for Assertions

: SQL assertions would check global constraints automatically.

Since Oracle does not implement assertions, developers use:

• triggers
• compound triggers
• procedural logic

But this makes constraint logic more complex and harder to maintain.

Key takeaways:

• Assertions are theoretical SQL features.
• Oracle uses triggers to simulate them.
• Triggers introduce issues like mutating tables, requiring advanced techniques like compound triggers.

Architectural Insight

Database designers prefer:

1. Declarative constraints (fast and simple)
2. Avoid triggers when possible
3. Move complex logic into application layer or stored procedures

This is another reason assertions were never really widely adopted.

Looking Ahead: Oracle and SQL Assertions

Another exciting development in the evolution of SQL within Oracle Database 26ai is the introduction of SQL Assertions.

Like we discussed in this article,Assertions allow database architects to define global integrity constraints that span multiple tables, enabling the database to enforce complex business rules directly at the schema level. While traditional constraints such as PRIMARY KEY, FOREIGN KEY, and CHECK operate within individual tables, assertions provide a mechanism to validate conditions across the entire database.

For example, organizations may want to ensure that the total budget allocated to departments never exceeds a global company limit, or that a customer cannot have more than a certain number of active contracts across multiple tables. SQL Assertions make it possible to enforce such rules declaratively.

Example from Oracle documentation:

By introducing SQL Assertions, Oracle continues to advance the power of SQL and reinforces a core philosophy of the platform: the database should be the central guardian of data integrity. Instead of scattering validation logic across multiple application layers, Oracle enables these rules to be defined once and enforced consistently for all applications accessing the data.

This approach further strengthens the position of Oracle Database as one of the most capable and forward-thinking enterprise database platforms, continuously expanding SQL in ways that help developers build more reliable, maintainable, and robust data systems.

In conclusion Oracle brings to market to most competitive commercial database product while investing and complying with the industry standards! Well done!

Now, we should look into migrating all the assertions workarounds implemented o take into use the new assertions functionality in 26 ai!

They are all valid use cases but, we should upgrade the system to 26ai first!

Disclaimer

All code excerpts presented in this article are provided for illustrative or educational purposes only. They may contain simplifications or pseudo-code and are not guaranteed to work in all environments. Any use, modification, or execution of the code is entirely at the reader’s own risk, and the authors accept no responsibility or liability for any consequences arising from its use.

We welcome feedback, corrections, or questions about this article — please reach out directly to the author at database@blog.parvu.org.

References