Constraints: the rules that keep bad data out
Constraints are rules the database enforces on every write — PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK and DEFAULT — so that rows which break them are refused instead of stored.
Why it matters
Application code has bugs, and data arrives from many places. Tom could sign up twice because nothing said emails must be unique. A rule in the database is checked on every insert and update, from every program, every time.
The idea
NOT NULL: a value is required.
UNIQUE: no two rows may share the value.
PRIMARY KEY: unique and not NULL — the row's identity.
FOREIGN KEY (REFERENCES): the value must exist in another table's key, and you choose what happens when that row is deleted (refuse, ON DELETE CASCADE, or ON DELETE SET NULL).
CHECK: any condition, such as price > 0 or plan IN ('free', 'pro').
If a write breaks a rule, the whole statement fails with an error and nothing is changed.
The rules at a passport desk. No photo, no passport (NOT NULL). One passport number per person (UNIQUE). The country you list must be a real country (FOREIGN KEY). Your age can't be negative (CHECK). The officer refuses the form; they don't "fix it later".
An officer might let a small mistake through. A constraint never does — which is the point, but it also means a bulk load can fail on row 9,999,999. Clean or check the data before loading, or load into a staging table first.
Syntax
email TEXT NOT NULL UNIQUE,
price INTEGER CHECK (price > 0),
dept_id INTEGER REFERENCES departments(dept_id) ON DELETE SET NULLHow it works
- Every INSERT and UPDATE is checked against every constraint on the table.
- subscribers.email is the primary key, so a second row with the same email is a conflict.
ON CONFLICT (email) DO NOTHINGtells the database to skip that row quietly instead of failing.- Inserting an order for customer 99, who doesn't exist, breaks the foreign key: refused.
- Inserting a department with an existing dept_id breaks the primary key: refused.
Key points
- NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and DEFAULT are the six to know.
- PRIMARY KEY = UNIQUE + NOT NULL, one per table.
- Foreign key delete options: refuse (default), ON DELETE CASCADE, ON DELETE SET NULL.
- A failed constraint fails the whole statement; nothing is half-written.
- Put rules in the database, not only in the app — the database sees every write.
Common mistakes
- Relying on the app alone to prevent duplicates (that is how Tom signed up twice).
- Using ON DELETE CASCADE without thinking: deleting one customer silently deletes all their orders.
- Expecting UNIQUE to stop two NULLs. In most databases it doesn't — NULL isn't equal to NULL.
Not true: A UNIQUE constraint stops a column from holding two NULLs.
In fact: In most databases NULL is not equal to NULL, so UNIQUE lets several NULLs in. Add NOT NULL as well when the column must always be filled.
In the interview
Constraints let the database enforce integrity on every write. NOT NULL is for required values, UNIQUE and PRIMARY KEY for identity, FOREIGN KEY for valid references with a chosen delete behaviour, and CHECK for any rule on a row. I put these rules in the schema rather than only in application code, because the database sees every writer, and a violation fails the statement atomically instead of leaving bad data behind.
Part of Unit 6: Building tables and changing data in the free SQL course.