Delete one department, and decide what happens to its people

01 The idea

Rules the data carries, not rules the code remembers

An integrity constraint is a condition on the data that the database checks itself, on every insert, update and delete. You write it once, in CREATE TABLE, and from that moment no statement can leave the table in a state that breaks it. There is nothing to call and nothing to remember. A statement that would break it does not partly succeed; it does not succeed at all.

The relational model names four families, and they arrive in a fixed order in every textbook and most interviews. Domain constraints say what values a single column may hold. Key constraints say which sets of columns identify a row uniquely. Entity integrity says no part of a primary key may be NULL. Referential integrity says a foreign key value must name a row that actually exists.

The first three can be decided by looking at one row of one table. Referential integrity is the only one that has to open a second table, and that difference is the whole of this lesson. It is also the one an application gets wrong, because the application checked a copy of the parent table a second ago rather than the table itself. And it raises a question the other three never do: when the row you point at is deleted, what should happen to you? SQL refuses to guess. You answer it in the schema, with ON DELETE and ON UPDATE.

02 Worked example

One INSERT, four checks, one refusal

Two tables for one college. Every employee belongs to a department, and the way that is written down is a dept_id column on employee that has to name a row in department. These are the rows on disk right now, and one row somebody is trying to add.

department                    employeedept_id  dept_name            emp_id  emp_name  badge  salary  dept_id-------  ---------            ------  --------  -----  ------  -------D1       Civil                E1      Asha      B7     40000   D1D2       Mining               E2      Ravi      B4     35000   D1                              E3      Meera     B9     52000   D2 -- the statement a clerk just ranINSERT INTO employee VALUES ('E4', 'Nikhil', 'B2', 30000, 'D9');

The server does not accept or reject that row in one go. It runs a series of checks, and the row survives all of them but one. Follow them left to right.

Node 4 is the only check that had to open a second table. Nodes 1 to 3 can all be decided from the row in front of you, which is exactly why an application can do them and gets away with it. Node 4 it cannot do honestly. An application that read department into memory at startup will wave 'D9' straight through, because what it is checking against is a copy, and the copy is as old as the last time it was refreshed.

Change one value and the same row goes in. Send 'D2' instead of 'D9' and all four checks pass, so node 5 writes the row instead of refusing it. Notice what is not checked either way: nothing here says an employee must have a department at all. If dept_id is left NULL, referential integrity is satisfied with nothing to compare, because a NULL foreign key points at no row and there is no row to go looking for. Only NOT NULL makes the link compulsory, and that turns out to matter enormously the day somebody deletes a department.

03 Mechanics

Every constraint you can declare, and what breaking it does

Seven things you can write in a column definition. Six of them are constraints, which means there is some row that would make them raise an error. One of them is not a constraint at all and is in the list because people put it there. The column that matters most is the third: when is this checked, because a rule that is checked at the wrong moment is a rule you cannot rely on.

Here are the same two tables with all of it declared. Every rule in this lesson is on this page, and none of it is anywhere else.

CREATE TABLE department (  dept_id   CHAR(2)      PRIMARY KEY,  dept_name TEXT         NOT NULL UNIQUE);CREATE TABLE employee (  emp_id    CHAR(2)      PRIMARY KEY,             -- the candidate key we picked  emp_name  TEXT         NOT NULL,  badge     CHAR(2)      NOT NULL UNIQUE,         -- the candidate key we did not  salary    NUMERIC(8,2) CHECK (salary >= 0),     -- a NULL salary passes this  dept_id   CHAR(2)      DEFAULT 'D1'              REFERENCES department(dept_id)              ON DELETE SET NULL              ON UPDATE CASCADE);

Line 2 is two constraints in one word. PRIMARY KEY is UNIQUE plus NOT NULL. The unique half is the key constraint; the not-null half is entity integrity, and it exists because the primary key is the only handle the relation gives you for naming a row. NULL means "unknown", an unknown identifier identifies nothing, and NULL = NULL does not even evaluate to true, so a NULL key could not be matched against anything.

Line 8 is the second candidate key, and the NOT NULL is doing real work. UNIQUE on its own would not give you a candidate key, because SQL lets a UNIQUE column hold NULL in many rows at once and a column that can be unknown cannot identify anything. NOT NULL UNIQUE is what makes badge a key you could have chosen as the primary key instead of emp_id. Both are candidate keys; both are minimal, since each is a single attribute; the primary key is whichever one you picked.

Line 9 is the trap that gets asked. CHECK (salary >= 0) rejects −1 and accepts NULL. A CHECK is violated only when it evaluates to FALSE, and NULL >= 0 evaluates to UNKNOWN. If a salary must be both present and non-negative, you need NOT NULL beside the CHECK, or you write the condition to catch it.

Lines 10 to 13 are one column and three separate decisions. DEFAULT says what to store when an INSERT leaves dept_id out. REFERENCES says the value has to exist in department. ON DELETE and ON UPDATE say what happens to this row when the department row it points at is deleted or changes its key. Note the quiet hazard in line 10: nothing maintains a DEFAULT that names a parent row, so delete D1 and every later INSERT that omits dept_id starts failing the foreign key for a value nobody typed.

What a violation does is worth being exact about, because half-right answers here are common. A constraint violation is a statement-level event: the statement has no effect at all, not on the rows before the offending one and not on the rows after it. A ten-thousand-row INSERT that trips a CHECK on row 9,999 writes nothing. What happens to the enclosing transaction is where products differ. PostgreSQL puts the whole transaction into an aborted state, so every statement after the error fails until you ROLLBACK or roll back to a savepoint. Oracle, MySQL and SQL Server on their usual settings undo only the failed statement and leave the transaction open. Give the statement-level answer unless you are asked about a specific product.

That leaves the question only foreign keys raise. Somebody deletes D1 while two employees still point at it. Refusing the delete is one answer, but it is not the only sensible one, so SQL makes you choose from five and defaults to the most cautious.

RESTRICT against NO ACTION is the question in this topic that separates people who have read the manual from people who have read a summary. Both refuse the delete, and on a plain single-statement delete you cannot tell them apart, which is why most notes call them the same thing. The standard defines the difference by timing: RESTRICT is checked before the delete is carried out, and NO ACTION after, once the row is gone and once any other referential action the same statement triggered has run. So if a second cascade in the same statement clears the offending children out of the way first, NO ACTION succeeds where RESTRICT would already have refused.

The difference you can actually reach on a running system is deferral. Declare the constraint DEFERRABLE INITIALLY DEFERRED and a NO ACTION check waits until COMMIT, so you may delete the parent now and repoint or delete the children later in the same transaction. RESTRICT can never be deferred, so it refuses immediately whatever you do. Say it that way and you have answered the question in one sentence: NO ACTION can be postponed, RESTRICT cannot.

Three vendor differences worth one clause each, and no more than that. Oracle supports ON DELETE CASCADE and ON DELETE SET NULL and nothing else: no RESTRICT keyword, no SET DEFAULT, and no ON UPDATE clause at all. MySQL's InnoDB accepts SET DEFAULT in the parser and then refuses to create the table, and because it has no deferred constraint checking it treats NO ACTION as a plain synonym for RESTRICT. SQLite implements all five and enforces none of them until the connection runs PRAGMA foreign_keys = ON. Name the standard behaviour first, then the vendor that departs from it; that order is what makes it sound like knowledge rather than trivia.

05 Cheat sheet

The five referential actions on one card

Learn the fourth column. Anyone can recite what CASCADE does; the follow-up is always "and when does that fail", and it is the only part of the table that is hard to guess.

06 Where & why

Where these words actually work

Every keyword in this lesson is a line you can type into a system that is running somewhere right now. None of them behaves identically everywhere, and knowing one specific departure is worth more in an interview than knowing the list twice.

07 Interview questions

What they actually ask

Constraints come up in almost every DBMS interview, usually as "what are the integrity constraints" and then one follow-up designed to find out whether you have ever hit one. The follow-up is nearly always about NULL or about what happens on a delete.

08 Practice problems

Six to work through

Write the rows down. Every one of these is decided by looking at an actual table state, and the people who get them wrong are the ones who answered from the schema without drawing what was in it.