Integrity Constraints: short notes (Database Management Systems)

Revision mode

Introduction

An integrity constraint is a condition that every legal database instance must satisfy. The DBMS checks it on every insert, delete and update, and rejects (or repairs) any change that would break it. Constraints move correctness rules out of application code and into the schema, where no program can bypass them.

The syllabus names integrity constraints directly. The three points that matter are which operation can violate which constraint, what each referential action (CASCADE, SET NULL, RESTRICT) does, and how far a cascading delete spreads through a self-referencing table.

Prerequisites

Relational Model and Keys: candidate, primary and foreign keys; NULL.

Core concepts

Constraint	Rule	SQL form
Domain constraint	Each value lies in the attribute's domain	data type, `CHECK (age >= 18)`
NOT NULL	The attribute may not be NULL	`NOT NULL`
Key constraint	No two tuples agree on a candidate key	`PRIMARY KEY`, `UNIQUE`
Entity integrity	No primary-key attribute is NULL	implied by `PRIMARY KEY`
Referential integrity	Every non-NULL foreign-key value appears as a key value in the referenced table	`FOREIGN KEY (x) REFERENCES T(k)`
General constraint	Any predicate over the database	`CHECK` on a row; `CREATE ASSERTION` for several tables

Terminology. In a foreign key from $r_1$ to $r_2$ , $r_1$ is the referencing (child) relation and $r_2$ the referenced (parent) relation. A child tuple whose foreign key matches no parent key is a dangling tuple; referential integrity forbids it.

UNIQUE and NULL. A UNIQUE column may hold NULL. In standard SQL and in most systems (including SQLite), several rows may hold NULL in a UNIQUE column, because NULL is not equal to NULL. Standard SQL requires every PRIMARY KEY component to be non-NULL. SQLite has a legacy exception for ordinary rowid-table primary-key declarations: rely on INTEGER PRIMARY KEY, WITHOUT ROWID or STRICT tables, or an explicit NOT NULL constraint to enforce this requirement.

Foreign keys and NULL. A foreign-key value that is NULL satisfies referential integrity automatically. With a composite foreign key, the default rule (MATCH SIMPLE) also accepts a row in which any one component is NULL.

How it works: which operation can violate what

This table considers one specified foreign key between distinct relations, holding all other roles fixed. A self-referencing row must be checked in both parent and child roles.

Operation	Domain, NOT NULL, key, entity	Referential integrity
Insert into the child	can violate	can violate (new value missing in the parent)
Insert into the parent	can violate	cannot violate
Delete from the child	cannot violate	cannot violate
Delete from the parent	cannot violate	can violate (children left dangling)
Update a child's foreign key	can violate	can violate
Update a parent's key	can violate	can violate

When an operation would violate referential integrity on the parent side, the foreign key's referential action decides what happens:

Action	On delete of a parent row	On update of a parent key
`NO ACTION` (default) / `RESTRICT`	reject the delete	reject the update
`CASCADE`	delete the matching child rows too	change the child foreign keys to the new value
`SET NULL`	set the child foreign keys to NULL	same
`SET DEFAULT`	set the child foreign keys to the column default; a non-NULL default must reference an existing parent key, while a NULL default can satisfy MATCH SIMPLE if column constraints permit	same

RESTRICT can reject an intermediate violation immediately, whereas NO ACTION may let a statement finish and test its final state (or the transaction state if deferred). They need not agree even within one statement; use the timing and dialect specified in the problem.

Three panels with the same parent DEPT rows 10 and 20 and child EMP rows e1 in 10, e2 in 20, e3 in 20, showing the effect of deleting department 20: RESTRICT rejects the delete, CASCADE removes e2 and e3, SET NULL keeps e2 and e3 with dept NULL — Figure 1. Deleting department 20 under the three referential actions: the delete is rejected, spreads to two employees, or leaves two employees with a NULL department.

Algorithms and pseudocode

A cascading delete is a graph search. With a self-referencing foreign key boss to id and ON DELETE CASCADE:

Text

CASCADE-DELETE(T, v)
  Q <- queue containing v; deleted <- empty set
  while Q is not empty
      u <- DEQUEUE(Q); add u to deleted
      for each row w of T with w.boss = u and w.id not in deleted
          ENQUEUE(Q, w.id)
  delete all rows whose id is in deleted

The rows deleted are exactly the rows reachable from $v$ in the "is boss of" graph, $v$ included. Time is $O(n)$ row visits with an index on boss, $O(n^2)$ without one.

Mathematical explanation

Referential integrity says the projection of the child on its foreign key, restricted to non-NULL values, is a subset of the parent's key projection:

\pi_{F}(\sigma_{F \text{ is not null}}(r_1)) \subseteq \pi_{K}(r_2)

where $F$ is the foreign key of $r_1$ and $K$ the referenced key of $r_2$ . The subset form explains the violation table: inserting into $r_2$ only enlarges the right side and deleting from $r_1$ only shrinks the left side, so neither can break the inclusion. The opposite two operations can.

A cascading delete keeps the inclusion true by removing from $r_1$ every tuple whose foreign key is no longer on the right; in a self-referencing table this repeats until no dangling tuple is left (a fixed point), which is the reachability computed by CASCADE-DELETE.

Worked examples

Example 1: self-referencing cascade. EMP(id, boss), boss references id ON DELETE CASCADE. Rows: (10, NULL), (11, 10), (12, 10), (13, 11), (14, 13), (15, 12), (16, 15), (17, 15).

Delete id = 11: 13 reports to 11 and 14 to 13, so 3 rows go (11, 13, 14); 5 rows remain.
Delete id = 12 instead: 12, 15, 16, 17 go, 4 rows; 4 remain.
Delete id = 10: every row is reachable from 10, so all 8 rows go.

Example 2: the same table with ON DELETE SET NULL. Deleting id = 12 removes 1 row and sets boss to NULL in 1 row (15); 16 and 17 still report to 15. The cascade stops after one level because no further row is deleted.

Example 3: a cycle. EMP(id, boss) with rows (1, 2) and (2, 1), each referencing the other, ON DELETE CASCADE. Inserted one row per statement, the first insert fails, because its boss does not exist yet. Three ways out: insert both rows in one statement (a NO ACTION foreign key is checked at the end of the statement), defer the check to commit, or insert one boss as NULL and update it later. Once both rows exist, deleting either row deletes both.

Example 4: which statements fail? DEPT(dno PRIMARY KEY) with rows 10, 20; EMP(eno PRIMARY KEY, dno REFERENCES DEPT) with no referential action, rows (e1, 10), (e2, 20).

Statement	Result	Reason
insert EMP (e3, 30)	rejected	30 is not a DEPT key
insert EMP (e3, NULL)	accepted	a NULL foreign key is allowed
insert DEPT (30)	accepted	inserting a parent never breaks the inclusion
delete EMP e1	accepted	deleting a child never breaks it
delete DEPT 20	rejected	e2 would dangle
insert EMP (e1, 20)	rejected	primary key e1 already exists

Important properties and observations

Entity integrity is about the primary key only. Other candidate keys declared UNIQUE may be NULL unless also declared NOT NULL.
Referential actions are declared on the child, but they react to changes in the parent.
CASCADE can chain through several tables and through self-references; count the rows reachable in the reference graph.
SET NULL fails if the child foreign-key column is NOT NULL (or part of the child's primary key).
Order of loading matters with circular foreign keys: defer the constraint, or insert with NULL and update later.
SQLite caveat. SQLite enforces foreign keys only after PRAGMA foreign_keys = ON; it enforces CHECK, NOT NULL, UNIQUE and PRIMARY KEY by default. Standard SQL always enforces declared foreign keys.

Related material. Triggers (event-condition-action rules) can enforce constraints that CHECK cannot express, such as "the salary of an employee does not exceed the salary of the manager". CREATE ASSERTION is part of standard SQL but few systems implement it.

Common misconceptions.

"Deleting a child row can violate referential integrity." It cannot violate the one specified FK when child and parent are distinct relations and all other roles are held fixed; self-referencing rows must be checked in both roles.
"A foreign key must reference the primary key." It may reference any candidate key declared UNIQUE.
"A foreign key cannot be NULL." It can, unless declared NOT NULL.

GATE examination perspective

Violation table. Which of insert or delete on the referencing or referenced relation can violate referential integrity: insert into child and delete from parent (plus updates).
Cascade counting. A self-referencing table with ON DELETE CASCADE: list the rows reachable from the deleted row.
Action semantics. Distinguish the effect of CASCADE, SET NULL and RESTRICT on the same delete, as in Figure 1.
Entity integrity versus UNIQUE. Which constraint forbids NULL.

Summary

Sources

A. Silberschatz, H. F. Korth, S. Sudarshan, Database System Concepts, 7th ed., McGraw-Hill, 2019 (2020 copyright), 4.4 Integrity Constraints.
R. Elmasri, S. B. Navathe, Fundamentals of Database Systems, 7th ed., Pearson, 5.2 Relational Model Constraints and Relational Database Schemas; 5.3 Update Operations, Transactions, and Dealing with Constraint Violations.
SQLite documentation, CREATE TABLE, Section 3.5 The PRIMARY KEY, (consulted 2026-11-11).