Integrity constraints and triggers

Why it exists

A database should refuse data that makes no sense: a student without a name, an enrolment in a course that does not exist, an age of 400. Integrity constraints state such rules declaratively; the system checks them on every change. Triggers go further: they run an action when something happens, which lets the database maintain derived data, keep an audit trail, or enforce rules that constraints cannot express.

Prerequisites

You need SQL tables and data modification (SQL basics) and the notions of keys and foreign keys (relational model and keys).

Precise definitions

Referential actions for a foreign key: ON DELETE / ON UPDATE followed by CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION (relational model and keys).

An assertion (CREATE ASSERTION name CHECK (condition)) is a constraint over several tables; the standard defines it but few systems implement it (SQLite does not), which is why triggers or application code are used.

A trigger is an event–condition–action rule:

CREATE TRIGGER name {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE [OF columns] | DELETE} ON table
[FOR EACH ROW] [WHEN (condition)]
BEGIN statements END;

Inside the body NEW is the row after the change and OLD the row before it (NEW for INSERT, OLD for DELETE). A row-level trigger (FOR EACH ROW) runs once for every changed row, a statement-level trigger once per statement (standard; SQLite has only row-level triggers).

How it works

What each constraint refuses

-- error
INSERT INTO Student (sid, sname) VALUES (9, NULL);

Error: NOT NULL constraint failed: Student.sname

-- error
INSERT INTO Student VALUES (1, 'Duplicate', 'CS', 30);

Error: UNIQUE constraint failed: Student.sid

The primary key refuses a second student with sid 1. UNIQUE allows several NULLs (as most systems do, because NULL is not equal to NULL):

CREATE TABLE Person (email TEXT UNIQUE);
INSERT INTO Person VALUES (NULL);
INSERT INTO Person VALUES (NULL);
SELECT COUNT(*) AS rows_with_null_email FROM Person;

rows_with_null_email
--------------------
2
(1 row)

A CHECK constraint is satisfied when the condition is TRUE or UNKNOWN; only FALSE is refused. So a NULL passes a CHECK (m BETWEEN 0 AND 100):

CREATE TABLE Marks (sid INTEGER, m INTEGER CHECK (m BETWEEN 0 AND 100));
INSERT INTO Marks VALUES (1, NULL);
INSERT INTO Marks VALUES (2, 100);
SELECT COUNT(*) AS accepted FROM Marks;

accepted
--------
2
(1 row)

-- error
CREATE TABLE Marks (sid INTEGER, m INTEGER CHECK (m BETWEEN 0 AND 100));
INSERT INTO Marks VALUES (3, 101);

Error: CHECK constraint failed: m BETWEEN 0 AND 100

Foreign keys: inserting and deleting

-- error
INSERT INTO Enrolled VALUES (99, 'C1', 'A');

Error: FOREIGN KEY constraint failed

An enrolment of a student that does not exist is refused. The opposite direction, deleting a referenced row, is refused under the default action (NO ACTION):

-- error
DELETE FROM Student WHERE sid = 1;

Error: FOREIGN KEY constraint failed

With ON DELETE CASCADE the referencing rows would be deleted too (shown on the page on keys). Only the primary key or a UNIQUE column can be referenced.

A statement is all or nothing

If one row of a multi-row statement violates a constraint, the whole statement is undone:

-- allow-errors
INSERT INTO Course VALUES ('C7', 'Compilers', 4, 'CS'), ('C1', 'Duplicate id', 3, 'CS'), ('C8', 'Graphics', 3, 'CS');
SELECT COUNT(*) AS courses_after_failed_insert FROM Course;

courses_after_failed_insert
---------------------------
6
(1 row)

The first statement failed on its second row (the key C1 exists), and the table still has the original 6 courses: C7, which was valid by itself, was not kept.

Deferred constraints

A foreign key is normally checked at the end of each statement. A constraint declared DEFERRABLE INITIALLY DEFERRED is checked at the end of the transaction, so rows that refer to each other can be inserted in any order inside it:

CREATE TABLE P (id INTEGER PRIMARY KEY);
CREATE TABLE C (pid INTEGER REFERENCES P(id) DEFERRABLE INITIALLY DEFERRED);
BEGIN;
INSERT INTO C VALUES (1);
INSERT INTO P VALUES (1);
COMMIT;
SELECT * FROM C;

pid
---
1
(1 row)

The child row was inserted before its parent and the commit succeeded because the parent existed at commit time. If it does not, the commit is refused:

-- error
CREATE TABLE P (id INTEGER PRIMARY KEY);
CREATE TABLE C (pid INTEGER REFERENCES P(id) DEFERRABLE INITIALLY DEFERRED);
BEGIN;
INSERT INTO C VALUES (2);
COMMIT;

Error: FOREIGN KEY constraint failed

Triggers

Auditing. After a grade changes, record the old and the new value:

CREATE TABLE AuditLog (sid INTEGER, cid TEXT, old_grade TEXT, new_grade TEXT);
CREATE TRIGGER log_grade AFTER UPDATE OF grade ON Enrolled FOR EACH ROW BEGIN INSERT INTO AuditLog VALUES (OLD.sid, OLD.cid, OLD.grade, NEW.grade); END;
UPDATE Enrolled SET grade = 'A' WHERE cid = 'C1' AND grade <> 'A';
SELECT * FROM AuditLog ORDER BY sid;

sid | cid | old_grade | new_grade
----+-----+-----------+----------
2   | C1  | B         | A
8   | C1  | B         | A
(2 rows)

The update changed two rows, so the row-level trigger ran twice. Chen's grade in C1 is NULL, and NULL <> 'A' is UNKNOWN, so his row was not updated and not logged.

Maintaining derived data. Keep a column that counts the students of a course:

ALTER TABLE Course ADD COLUMN taken INTEGER DEFAULT 0;
UPDATE Course SET taken = (SELECT COUNT(*) FROM Enrolled e WHERE e.cid = Course.cid);
CREATE TRIGGER count_enrol AFTER INSERT ON Enrolled FOR EACH ROW BEGIN UPDATE Course SET taken = taken + 1 WHERE cid = NEW.cid; END;
INSERT INTO Enrolled VALUES (4, 'C1', 'B');
SELECT cid, taken FROM Course WHERE cid IN ('C1', 'C2') ORDER BY cid;

cid | taken
----+------
C1  | 6
C2  | 3
(2 rows)

C1 had 5 enrolments; the trigger made it 6, C2 is unchanged. (A derived column can go out of date if some statement changes Enrolled in a way the triggers do not cover, for example DELETE; a view or a query avoids that risk.)

Vetoing a change. A BEFORE trigger can refuse an operation with a message:

-- error
CREATE TRIGGER check_age BEFORE INSERT ON Student FOR EACH ROW WHEN NEW.age < 16 BEGIN SELECT RAISE(ABORT, 'student too young'); END;
INSERT INTO Student VALUES (9, 'Ishan', 'CS', 14);

Error: student too young

Making a join view updatable. An INSTEAD OF trigger replaces the operation on a view by the actions that the designer chooses:

CREATE VIEW CSGrades AS SELECT s.sid, s.sname, e.cid, e.grade FROM Student s JOIN Enrolled e ON s.sid = e.sid WHERE s.dept = 'CS';
CREATE TRIGGER upd_grade INSTEAD OF UPDATE OF grade ON CSGrades FOR EACH ROW BEGIN UPDATE Enrolled SET grade = NEW.grade WHERE sid = OLD.sid AND cid = OLD.cid; END;
UPDATE CSGrades SET grade = 'A' WHERE sname = 'Asha' AND cid = 'C2';
SELECT grade FROM Enrolled WHERE sid = 1 AND cid = 'C2';

grade
-----
A
(1 row)

Row-level and statement-level. An update of the four CS students runs a row-level trigger four times:

CREATE TABLE Log (n INTEGER);
CREATE TRIGGER per_row AFTER UPDATE ON Student FOR EACH ROW BEGIN INSERT INTO Log VALUES (NEW.sid); END;
UPDATE Student SET age = age + 1 WHERE dept = 'CS';
SELECT COUNT(*) AS trigger_runs FROM Log;

trigger_runs
------------
4
(1 row)

A statement-level trigger (standard SQL) would run once for the whole statement.

Constraints against triggers

Prefer a declarative constraint when it can express the rule: the system optimises it and it cannot be forgotten. Use a trigger for what constraints cannot say: rules across several tables, derived data, auditing, complex reactions. Triggers have costs: they can fire other triggers (cascades, possibly cyclic), they hide behaviour, and the order of several triggers is not always defined.

Worked examples

Cheat box

Traps and GATE patterns

Connections

Constraint checks and triggers run inside transactions that are atomic and isolated (transactions and ACID); views and NULL behaviour are on the page on nested queries.

PYQs

Consider the following tables T1 and T2.

In table T1, P is the primary key and Q is the foreign key
referencing R in table T2 with on-delete cascade. In table
T2, R is the primary key and S is the foreign key
referencing P in table T1 with on-delete set NULL and on-
update cascade. In order to delete record (3, 8) from table
T1, the number of additional records that need to be deleted
from table T1 is _____.

The following table has two attributes A and C where A
is the primary key and C is the foreign key referencing
A with ON DELETE CASCADE.

The set of all tuples that must be additionally deleted to
preserve referential integrity when the tuple (2, 4) is
deleted is:

Given the following statements:
S1: A foreign key declaration can always be replaced by
an equivalent check assertion in SQL.
S2: Given the table R(a,b,c) where a and b together form
the primary key, the following is a valid table definition.

CREATE TABLES (

a INTEGER,

d integer,

e integer,

PRIMARY KEY (d),

FOREIGN key (a) references R)

Which one of the following statements is CORRECT?

Self-check

Revision card

References