One of the most frustrating things I’ve dealt with as a DBA is cleaning up data that should never have existed in the first place. Orphaned records, overlapping date ranges, business rules violated because some batch job skipped a validation step. We’ve all been there.
The traditional solution was triggers. And if you’ve written cross-table validation triggers in Oracle, you know the pain: mutating table errors (ORA-04091), complex exception handling, scattered logic across multiple trigger bodies, and debugging sessions that make you question your career choices.
Starting with Oracle Database 23ai (release 23.26.1), Oracle introduced SQL Assertions, and they change everything about how we enforce cross-table business rules.
What Are SQL Assertions?
An assertion is a schema-level integrity constraint defined by a boolean expression. If that expression evaluates to false during a transaction, the transaction fails. That’s it. The concept has been part of the SQL standard since SQL-92, but no major database vendor actually implemented it until Oracle did it in 23.26.1.
There are two types of assertion expressions:
Existential expressions use [NOT] EXISTS with a subquery. If the condition is true, the transaction proceeds.
Universal expressions use the new ALL ... SATISFY syntax. This lets you say “for every row matching this query, this condition must hold.” It’s Oracle’s elegant alternative to the awkward double-negation pattern (NOT EXISTS ... WHERE NOT EXISTS ...) that SQL traditionally requires for universal quantification.
The Scenario: SLA Compliance for a Ticketing System
Let me show you a real-world use case that goes beyond toy examples. Imagine you run a support ticketing system for an enterprise. You have service level agreements (SLAs) with your customers, and the database needs to enforce these rules:
- Every customer must have an active SLA before they can submit a ticket. No SLA, no support.
- Tickets can only be created while the customer’s SLA is active (between start and end dates).
- High-priority tickets must be assigned to a senior engineer. You can’t assign a critical production issue to a junior team member.
- Every SLA must cover at least one service category. An SLA with no covered services is meaningless.
In a traditional Oracle setup, enforcing these rules would require at least four separate triggers across three tables, careful handling of mutating table errors, and a lot of testing to make sure they don’t interfere with each other.
With assertions, each rule is a single declarative statement.
Building the Schema
sql
DROP TABLE IF EXISTS tickets CASCADE CONSTRAINTS PURGE;DROP TABLE IF EXISTS sla_services CASCADE CONSTRAINTS PURGE;DROP TABLE IF EXISTS slas CASCADE CONSTRAINTS PURGE;DROP TABLE IF EXISTS engineers CASCADE CONSTRAINTS PURGE;DROP TABLE IF EXISTS customers CASCADE CONSTRAINTS PURGE;CREATE TABLE customers ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR2(200) NOT NULL, company VARCHAR2(200), created_at TIMESTAMP DEFAULT SYSTIMESTAMP);CREATE TABLE engineers ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR2(200) NOT NULL, seniority VARCHAR2(20) CHECK ( seniority IN ('junior','mid','senior','lead') ), specialization VARCHAR2(100));CREATE TABLE slas ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id NUMBER NOT NULL REFERENCES customers(id), sla_tier VARCHAR2(20) CHECK ( sla_tier IN ('bronze','silver','gold','platinum') ), start_date DATE NOT NULL, end_date DATE NOT NULL, CONSTRAINT sla_dates_valid CHECK (end_date > start_date));CREATE TABLE sla_services ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, sla_id NUMBER NOT NULL REFERENCES slas(id), service_name VARCHAR2(100) NOT NULL);CREATE TABLE tickets ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id NUMBER NOT NULL REFERENCES customers(id), engineer_id NUMBER REFERENCES engineers(id), priority VARCHAR2(20) CHECK ( priority IN ('low','medium','high','critical') ), subject VARCHAR2(500) NOT NULL, created_at TIMESTAMP DEFAULT SYSTIMESTAMP, status VARCHAR2(20) DEFAULT 'open' CHECK ( status IN ('open','in_progress','resolved','closed') ));
Assertion 1: Customers Need an Active SLA to Submit Tickets
This is the core business rule. No active SLA, no ticket creation.
sql
CREATE ASSERTION ticket_requires_active_slaCHECK ( ALL (SELECT customer_id, created_at FROM tickets) SATISFY EXISTS ( SELECT 1 FROM slas WHERE slas.customer_id = tickets.customer_id AND tickets.created_at BETWEEN slas.start_date AND slas.end_date ));
Read that in plain English: “For all tickets, there must exist an SLA for that customer where the ticket creation date falls within the SLA period.”
If someone tries to insert a ticket for a customer whose SLA has expired, the database will reject the transaction. No application code needed. No trigger needed. The rule is declarative and self-documenting.
Assertion 2: High-Priority Tickets Need Senior Engineers
This is a cross-table constraint that would be especially painful with triggers because it spans tickets and engineers.
sql
CREATE ASSERTION critical_tickets_need_senior_engineerCHECK ( NOT EXISTS ( SELECT 1 FROM tickets t JOIN engineers e ON t.engineer_id = e.id WHERE t.priority IN ('high', 'critical') AND e.seniority IN ('junior', 'mid') ));
This uses the existential pattern. It looks for any high-priority ticket assigned to a junior or mid-level engineer. If it finds one, the transaction fails. Simple, clear, and impossible to bypass from any application that touches this database.
Assertion 3: Every SLA Must Cover at Least One Service
An SLA without any covered services is a data integrity problem waiting to happen.
sql
CREATE ASSERTION sla_must_have_servicesCHECK ( ALL (SELECT id FROM slas) SATISFY EXISTS ( SELECT 1 FROM sla_services WHERE sla_services.sla_id = slas.id ))DEFERRABLE INITIALLY DEFERRED;
This one uses DEFERRABLE INITIALLY DEFERRED because of the chicken-and-egg problem: the foreign key on sla_services requires the SLA to exist first, but this assertion requires services to exist when an SLA exists. By deferring validation to commit time, you can insert both the SLA and its services in a single transaction.
Testing It Out
Let’s load some data and see the assertions in action:
sql
-- Insert customersINSERT INTO customers (name, company) VALUES ('Ahmad Hassan', 'TechCorp Jordan');INSERT INTO customers (name, company) VALUES ('Sara Ali', 'DataFlow ME');-- Insert engineersINSERT INTO engineers (name, seniority, specialization)VALUES ('Omar Khalid', 'senior', 'Database');INSERT INTO engineers (name, seniority, specialization)VALUES ('Lina Nasser', 'junior', 'Networking');-- Insert SLA with services (in one transaction -- because of deferred assertion)INSERT INTO slas (customer_id, sla_tier, start_date, end_date)VALUES (1, 'gold', DATE '2025-01-01', DATE '2026-12-31');INSERT INTO sla_services (sla_id, service_name)VALUES (1, 'Database Support');INSERT INTO sla_services (sla_id, service_name)VALUES (1, '24/7 Monitoring');COMMIT; -- Assertion validates here: SLA has services, OK-- This should succeed: customer has active SLA, -- senior engineer assignedINSERT INTO tickets (customer_id, engineer_id, priority, subject)VALUES (1, 1, 'critical', 'Production database performance issue');COMMIT;
Now let’s try violating the rules:
sql
-- This should FAIL: assigning critical ticket -- to junior engineerINSERT INTO tickets (customer_id, engineer_id, priority, subject)VALUES (1, 2, 'critical', 'Server outage');COMMIT;-- ERROR: assertion CRITICAL_TICKETS_NEED_SENIOR_ENGINEER violated-- This should FAIL: customer 2 has no SLAINSERT INTO tickets (customer_id, engineer_id, priority, subject)VALUES (2, 1, 'low', 'General question');COMMIT;-- ERROR: assertion TICKET_REQUIRES_ACTIVE_SLA violated
The database enforces the rules. Every time. Regardless of which application, API, or batch job is inserting the data.
Why This Matters
The traditional approach to these rules would involve:
- Four or more
BEFORE INSERTtriggers across multiple tables - Careful handling of
ORA-04091mutating table errors (probably using compound triggers or package variables) - Testing every combination of insert/update/delete across all tables
- Documentation that explains what each trigger does and how they interact
- A maintenance burden that grows with every new business rule
With assertions, each rule is one statement. They live in the data dictionary alongside your other constraints. You can query USER_CONSTRAINTS to see them. They are self-documenting. And Oracle’s internal incremental checking mechanism ensures they perform well because the database only validates the data that actually changed, not the entire table.
Practical Notes
Grant the privilege. CREATE ASSERTION is not included in RESOURCE. Use GRANT DB_DEVELOPER_ROLE TO your_user; or grant it explicitly.
Assertions share the constraint namespace. You cannot have an assertion and a constraint with the same name in the same schema.
Cross-schema assertions need ASSERTION REFERENCES. If your assertion references tables in another schema, you need this object privilege on those tables, and you must use fully qualified table names (synonyms are not supported).
Start with ENABLE NOVALIDATE on existing systems. This lets you add an assertion without checking existing data, which is essential when adding rules to a database that might already contain violations.
Subqueries can nest up to three levels. For most business rules, this is more than enough.
Resources
- CREATE ASSERTION documentation
- Assertion concepts documentation
- How to define cross-table constraints with assertions by Chris Saxon
- Oracle AI Database Free container for local testing
- FreeSQL for browser-based experimentation
Thank you
Osama
Technology Geek 