Integrity Constraints

The previous lesson rejected a foreign key violation but did not name the rule behind it. This lesson’s question is: what rules must a database satisfy at every instant, how are these rules written into the schema, and which behaviors can the system choose when a delete arrives that would break one?

Constraints fall into three layers. Domain integrity states which values may be written to a column — type, NOT NULL, and CHECK are its tools and were seen in the previous lesson. Entity integrity requires that every row’s identity be determined. Referential integrity requires that a reference from one relation to another never point nowhere. A fourth layer is domain-specific business rules; these too are written into the schema wherever possible.

Entity Integrity

The rule is one sentence: no column making up the primary key may take a null value. Its justification follows directly from the key’s definition. A key’s job is to distinguish a row; a null value means “unknown.” A row whose identity is unknown is a row that cannot be referenced.

That this rule stands in the standard does not mean every engine enforces it with the same strictness:

sqlite3 :memory: <<'SQL'
.headers on
.mode box
CREATE TABLE branch (branch_code TEXT PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO branch VALUES (NULL, 'Unnamed Branch');
SELECT COUNT(*) AS row_count FROM branch;
SQL

┌───────────┐
│ row_count │
├───────────┤
│ 1         │
└───────────┘

A row with a null primary key entered the table. This behavior varies by engine; some reject the same statement outright. A portable schema with a clear intent writes the rule itself:

sqlite3 :memory: <<'SQL'
CREATE TABLE branch (branch_code TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL);
INSERT INTO branch VALUES (NULL, 'Unnamed Branch');
SQL

Runtime error near line 2: NOT NULL constraint failed: branch.branch_code (19)

The general principle that follows is one of the course’s recurring themes: writing a rule explicitly into the schema, rather than assuming the engine already enforces it, improves both portability and readability.

Referential Integrity

The rule is this: a foreign key’s value is either null, or a row exists in the referenced relation carrying that value. There is no third possibility; a row whose target cannot be found is called an orphan record.

Whether this rule is enforced also varies by engine. In the command-line tool used here, the check is off by default and needs to be turned on per session:

sqlite3 :memory: <<'SQL'
.headers on
.mode box
PRAGMA foreign_keys;
CREATE TABLE member (member_no INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE loan (
  loan_no   INTEGER PRIMARY KEY,
  member_no INTEGER NOT NULL REFERENCES member (member_no)
);
INSERT INTO loan VALUES (1001, 77);
SELECT COUNT(*) AS orphan_record FROM loan;
SQL

┌──────────────┐
│ foreign_keys │
├──────────────┤
│ 0            │
└──────────────┘
┌───────────────┐
│ orphan_record │
├───────────────┤
│ 1             │
└───────────────┘

REFERENCES is written into the schema, the member table is empty, and yet a loan record went in anyway. Because the check was off, the declaration carried only documentary value. The blocks for the rest of this lesson begin with the line that turns the check on.

Delete and Update Actions

Referential integrity can break at two moments: when the referenced row is deleted, and when the referenced key value is updated. The schema chooses a behavior for each of these two moments.

• NO ACTION / RESTRICT — the operation is rejected. This is the standard’s default behavior; the difference between the two lies in the timing of the check.
• CASCADE — the delete or update propagates to the referencing rows.
• SET NULL — the referencing row’s foreign key is cleared; the column must accept a null value.
• SET DEFAULT — the referencing row’s foreign key is set to the column’s default value; that value must exist at the target.

The choice is not arbitrary; it follows the domain’s meaning. When a member is deleted, leaving their penalty records in place is meaningless — these records are dependent on the member and cannot exist on their own, so CASCADE. When a branch closes, the member’s record should not be deleted; the member is left without a branch, so SET NULL. A member’s loan history, however, should not be deleted — a past record is a fact — so the delete is rejected there.

sqlite3 :memory: <<'SQL'
.headers on
.mode box
.nullvalue (null)
PRAGMA foreign_keys = ON;
CREATE TABLE branch (branch_code TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL);
CREATE TABLE member (
  member_no   INTEGER PRIMARY KEY,
  name        TEXT NOT NULL,
  branch_code TEXT REFERENCES branch (branch_code) ON DELETE SET NULL
);
CREATE TABLE penalty (
  penalty_no INTEGER PRIMARY KEY,
  member_no  INTEGER NOT NULL REFERENCES member (member_no) ON DELETE CASCADE,
  amount     INTEGER NOT NULL
);
INSERT INTO branch VALUES ('CEN', 'Central'), ('BHC', 'Bahcelievler');
INSERT INTO member VALUES (41, 'Alice Kane', 'CEN'), (52, 'Marcus Reyes', 'BHC');
INSERT INTO penalty VALUES (9001, 52, 30), (9002, 52, 15);
DELETE FROM member WHERE member_no = 52;
DELETE FROM branch WHERE branch_code = 'CEN';
SELECT (SELECT COUNT(*) FROM member)  AS member_rows,
       (SELECT COUNT(*) FROM penalty) AS penalty_rows,
       (SELECT branch_code FROM member WHERE member_no = 41) AS member41_branch;
SQL

┌─────────────┬──────────────┬─────────────────┐
│ member_rows │ penalty_rows │ member41_branch │
├─────────────┼──────────────┼─────────────────┤
│ 1           │ 0            │ (null)          │
└─────────────┴──────────────┴─────────────────┘

A single member was deleted, and the two penalty records bound to them went too; a branch was deleted, and the member bound to it was left without a branch. The .nullvalue setting exists only to make the null value visible in the output — without it, a null value and an empty string cannot be told apart on screen.

The default behavior is rejection:

sqlite3 :memory: <<'SQL'
PRAGMA foreign_keys = ON;
CREATE TABLE member (member_no INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE loan (
  loan_no   INTEGER PRIMARY KEY,
  member_no INTEGER NOT NULL REFERENCES member (member_no)
);
INSERT INTO member VALUES (41, 'Alice Kane');
INSERT INTO loan VALUES (1001, 41);
DELETE FROM member WHERE member_no = 41;
SQL

Runtime error near line 9: FOREIGN KEY constraint failed (19)

Care is needed with CASCADE: propagation chains. A statement that deletes a member also deletes their penalty records, any payment records bound to those, and the continuation of that chain. The number of rows a delete removes can be far larger than the person writing the statement expects.

When a Constraint Is Checked

Constraints are checked by default at the end of every statement. This can prevent some valid states from being temporarily constructed. If two relations reference each other — every branch has a manager and every manager works at a branch — then whichever gets inserted first, the other side does not yet exist at the moment of insertion.

The remedy is to postpone the check to the end of the transaction; this is called a deferred constraint. Under deferred checking, intermediate states may break the rule, as long as the rule holds once the transaction completes. Whether this capability exists and how it is declared varies by engine; where it does not exist, one end of the mutual reference must accept a null value and be filled in over two steps.

Where to Write a Constraint

Not every rule fits the schema. The rule “a member may hold at most five copies on loan at once” cannot be checked by looking at a single row; it requires a count. Rules like this are written either with a trigger or in the application layer.

The criterion is this: if a rule holds for every path that reaches the data and can be expressed over a single row or row set, it is written into the schema. A constraint in the schema is enforced independent of which program is writing, in which language it is written, and whether the data is touched by hand or through a bulk migration. A check in the application layer covers only writes that pass through that application; a second service, a maintenance script, or a manually run statement bypasses it.

A constraint’s second function is documentation. Someone reading the schema learns from a line that says ON DELETE CASCADE that a penalty record depends on a member. The same fact, scattered through application code, goes unread.

Summary

• Entity integrity means the primary key cannot take a null value; a row of unknown identity cannot be referenced.
• Referential integrity requires a foreign key to be either null or matched at the target; a violation produces an orphan record.
• Writing a constraint explicitly into the schema, rather than assuming the engine enforces it by default, improves portability and readability.
• Delete and update actions — reject, propagate, clear, reset to default — are chosen according to the domain’s meaning; propagation chains and can delete more rows than expected.
• Rules that bind every write path and can be expressed at the row level belong in the schema; those requiring a count or history belong in the application layer.

Next Step

This lesson said “or it is null” twice: a foreign key may be null, SET NULL clears a column. What a null value means was left undefined. The next lesson shows that a null value is not a value, what comparisons do with it, and what traps adding a third result to the true–false pair opens up in query writing.