Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 79 ยท The Relational Model

Enforcing Integrity: Constraints, Assertions and Triggers

See how a DBMS enforces rules: declarative CHECK, UNIQUE and deferred constraints, SQL assertions, and triggers as the last resort. Live SQLite examples.

A database is only as trustworthy as the rules it refuses to break. Keys and Integrity Constraints introduced the rules themselves: primary keys, foreign keys and domain checks. This lesson is about the machinery: where a rule should live, how the DBMS enforces it, and what to do when the rule is too complicated for a column definition.

The guiding idea is simple. Every rule you can state declaratively (as a constraint the engine understands) is better than a rule you code procedurally (as a trigger or application code). The engine can reason about declarative rules, optimize around them, and never forgets to apply them. Procedural rules are powerful but easy to get wrong. We will go from the cheapest tool to the most expensive one.

Declarative or procedural?

Three places to put a rule

Declarative PRIMARY KEY, UNIQUE, NOT NULL, CHECK, FOREIGN KEY.
Triggers Code the engine runs on INSERT, UPDATE or DELETE.
Application code Checks in your program. Easy to bypass.

Prefer the leftmost option that can express the rule.

1 / 2

An application check is the weakest option. Another program, a data import script or a person typing into a console can all reach the tables without going through your code. A rule enforced inside the DBMS holds for every writer, which is the whole point of a shared database (File Systems vs a DBMS).

Row-level rules: NOT NULL, CHECK and UNIQUE

A CHECK constraint is a Boolean expression evaluated against one row. If the expression is false, the statement is rejected. A UNIQUE constraint says no two rows may share the same value (or combination of values) in the listed columns.

SQL CHECK, UNIQUE and NOT NULL working together

Press Run (or Ctrl + Enter) to see the result here.

The last CHECK (length(sku) = 6) is a table constraint: it is written after the columns and may mention several of them. The others are column constraints. The engine treats both the same way. Now break the rules on purpose.

SQL Rejected: a row that breaks a CHECK

Press Run (or Ctrl + Enter) to see the result here.

The run ends with CHECK constraint failed: price >= 0, and the bad row never reaches the table. Other violations produce similar messages: a duplicate sku gives UNIQUE constraint failed: products.sku, and an UPDATE that would push stock below zero is refused the same way as an INSERT would be. Constraints guard every change, not just inserts.

CHECK passes on NULL. A CHECK only rejects a row when the expression is false. If a column is nullable and the expression becomes unknown (for example NULL >= 0), the row is accepted. That follows the three-valued logic from Nulls and Three-Valued Logic. If a value must be present, add NOT NULL as well.

What CHECK cannot do

The SQL standard allows a CHECK to contain a subquery, which would let one table's rule look at another table's rows. Almost no real engine allows it, and SQLite explicitly refuses it with the message subqueries prohibited in CHECK constraints. A CHECK is therefore limited to the values of the row being written. Anything that needs to look at other rows (a total, a count, a comparison with another table) needs a stronger tool.

Deferred constraints

By default a constraint is checked at the end of each statement. That is a problem for a circular rule. Imagine a dept table whose head is an employee, and an emp table whose dept is a department. Whichever row you insert first points at a row that does not exist yet.

The standard solution is a deferrable constraint: the engine postpones the check until COMMIT, so the whole transaction only has to be consistent at the moment it ends. SQLite supports this for foreign keys, once foreign-key enforcement is turned on.

SQL Deferred foreign keys: a circular reference

Press Run (or Ctrl + Enter) to see the result here.

Between the two inserts the database is briefly inconsistent, and that is allowed, because the check runs at COMMIT. If the second insert were missing, the COMMIT itself would fail with FOREIGN KEY constraint failed and the transaction could not be completed.

Deferral is a feature of foreign keys in SQLite. Full SQL-standard systems such as PostgreSQL also let you defer UNIQUE, primary key and exclusion constraints, and let one transaction switch modes with SET CONSTRAINTS ALL DEFERRED. That syntax is not available in SQLite, so treat it as background knowledge. CHECK constraints are never deferrable in SQLite.

Assertions: the rule the standard promised

The SQL standard defines a CREATE ASSERTION statement: a named condition on the whole database that must always hold. It is what you would want for rules that span rows or tables. A typical example is "no department may have a total salary above its budget".

SQL
-- Standard SQL. Shown only: SQLite and most other engines do not implement it.
CREATE ASSERTION salary_within_budget CHECK (
  NOT EXISTS (
    SELECT 1
    FROM departments AS d
    WHERE d.budget < (SELECT SUM(e.salary)
                      FROM employees AS e
                      WHERE e.dept_id = d.id)
  )
);

The block above is read-only on this site, and honest advice goes further: you will not be able to run it on SQLite, PostgreSQL, MySQL, SQL Server or Oracle either. Assertions are expensive to check (the engine would have to work out which changes could possibly affect the condition), so vendors skipped them. In practice the same idea is implemented with the next tool.

Triggers as the last resort

A trigger is a stored piece of code that the DBMS runs automatically when a table is modified. It follows the event-condition-action pattern: on this event, if this condition holds, do this action. Triggers can see the rows being changed (NEW and OLD) and read any other table, which makes them able to enforce what CHECK cannot. Running them in detail is covered in Triggers, Stored Procedures and Cursors; here we use them for integrity.

The next example enforces a cross-row rule: a room may not hold more bookings than its capacity.

SQL A trigger enforces a capacity rule

Press Run (or Ctrl + Enter) to see the result here.

Two guests fit. If you add a third booking for room 1 at the end of this script, the trigger fires, RAISE(ABORT, ...) cancels the statement, and the run stops with room is full; the table keeps its two rows. Try it.

Triggers also record history rather than refuse changes. An audit trigger writes a log row every time a sensitive column changes:

SQL An audit trigger records every balance change

Press Run (or Ctrl + Enter) to see the result here.

The log shows 1 | 100 | 70 and 2 | 50 | 80. Note that the nonnegative-balance rule is still a plain CHECK: the trigger only handles the part that a constraint cannot.

Why "last resort"?

Triggers are tempting, but they carry real costs:

  • Hidden behavior. A simple INSERT quietly does more work. Developers forget triggers exist.
  • Race conditions. The capacity trigger above counts rows and then inserts. Two concurrent transactions can both see "one seat left". Real servers need locking or a stricter isolation level (Isolation Levels and Concurrency Anomalies), or the rule can still be broken.
  • Cost. The trigger runs for every affected row, and its subqueries need indexes (Indexing Basics).
  • Portability. Trigger syntax differs between systems, and the declarative constraints do not.

The rule of thumb: use a data type and NOT NULL, then keys, then CHECK, then foreign keys; reach for a trigger only for rules involving other rows or other tables.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A CHECK constraint on a nullable column evaluates to unknown for a row. What does the DBMS do?

    A CHECK fails only when its expression is false. Unknown (from NULL) lets the row through, so add NOT NULL when a value is mandatory.
  2. 2. Which rule cannot be written as a SQLite CHECK constraint?

    That rule needs to count rows in another table. SQLite rejects subqueries in CHECK, so a trigger (or an assertion in a system that had one) is needed.
  3. 3. What does a DEFERRABLE INITIALLY DEFERRED foreign key change?

    Deferral lets a transaction pass through temporarily inconsistent states, as long as everything is consistent at COMMIT.
  4. 4. Why should triggers be the last choice for enforcing a rule?

    Declarative constraints are visible in the schema, always applied and optimizable. Triggers are powerful but hide logic and need care under concurrency.
  5. 5. Which statement about CREATE ASSERTION is true?

    The standard describes database-wide assertions, but engines skipped them because checking them is expensive. Triggers fill the gap.

Summary

  • Put each rule in the most declarative form that can express it: type, NOT NULL, PRIMARY KEY/UNIQUE, CHECK, FOREIGN KEY, then a trigger.
  • CHECK sees one row only and rejects a row only when the expression is false, so NULL slips through unless NOT NULL is also declared.
  • Deferrable constraints are checked at commit; in SQLite that applies to foreign keys, which solves circular references.
  • CREATE ASSERTION is standard SQL but essentially never implemented; triggers enforce cross-row and cross-table rules instead.
  • Triggers hide logic, can race under concurrency and cost time on every write, so keep them for what constraints cannot say.

Next: the algebra behind queries, starting with Selection, Projection and Renaming.