Tag: sql

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:

1. Every customer must have an active SLA before they can submit a ticket. No SLA, no support.
2. Tickets can only be created while the customer’s SLA is active (between start and end dates).
3. High-priority tickets must be assigned to a senior engineer. You can’t assign a critical production issue to a junior team member.
4. 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_sla
CHECK (
    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_engineer
CHECK (
    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_services
CHECK (
    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 customers
INSERT INTO customers (name, company) 
VALUES ('Ahmad Hassan', 'TechCorp Jordan');
INSERT INTO customers (name, company) 
VALUES ('Sara Ali', 'DataFlow ME');
-- Insert engineers
INSERT 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 assigned
INSERT 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 engineer
INSERT 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 SLA
INSERT 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 INSERT triggers across multiple tables
• Careful handling of ORA-04091 mutating 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