Keys and constraints
A table's definition can carry rules, and the DBMS checks every change against them. Meet the five everyday constraints (PRIMARY KEY, NOT NULL, UNIQUE, CHECK and FOREIGN KEY) by sending the same new book, Stardust, five times with one broken value each time. Watch PostgreSQL refuse each try with its own error, store the sixth, and then refuse to delete an author who still has books.
5 quick questions
Pick an answer to see why. Your score saves with your progress.
01books has a PRIMARY KEY on id. Which INSERT does PostgreSQL refuse?
A primary key must be unique and not null. A repeated id gives: duplicate key value violates unique constraint "books_pkey".
02shelf is UNIQUE. Two new books both leave shelf empty (NULL). What happens in PostgreSQL by default?
By default PostgreSQL treats NULLs as distinct in a unique constraint, so many rows may leave the column empty. UNIQUE NULLS NOT DISTINCT (PostgreSQL 15 and later) allows at most one.
03The rule is CHECK (year > 0). Which year is stored?
A CHECK passes when its test is true or NULL. NULL > 0 is NULL, so a missing year gets through. Add NOT NULL to the column to require a year.
04Coraline and Stardust have author_id 2. What does DELETE FROM authors WHERE id = 2 do with the default foreign key?
The default action is NO ACTION, so the delete is refused while any book points at author 2. ON DELETE CASCADE or ON DELETE SET NULL choose the other behaviours.
05In Python's sqlite3, a book with author_id 9 (no such author) is stored without an error. Why?
SQLite understands REFERENCES, but for backward compatibility it only enforces it after PRAGMA foreign_keys = ON on each connection.
Glossary
- Constraint
- A rule written into a table's definition that every row must obey. The DBMS refuses any change that would break it.
- Primary key
- The column (or columns) that names each row: values must be unique and never NULL. A table has at most one.
- NOT NULL
- A rule that a column must always hold a value.
- NULL
- SQL's marker for "no value here". It is not zero and not an empty string.
- UNIQUE
- A rule that no two rows share the same value in a column. Unlike a primary key, it allows NULLs.
- CHECK
- A rule with a test, such as year > 0, that every row must pass (true or NULL).
- Foreign key
- A column whose values must match a key in another table, such as books.author_id REFERENCES authors (id).
- Referential integrity
- The guarantee a foreign key gives: no row points at a row that does not exist.
Notes and sources
- Each try in the figure breaks exactly one rule. A row that breaks several gets one error: PostgreSQL stops at the first rule it finds broken.
- A UNIQUE column may hold many NULLs, because PostgreSQL treats two NULLs as different by default. PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT to allow at most one.
- A CHECK passes when its test is true or NULL, so CHECK (year > 0) lets a missing year through. Add NOT NULL as well to require one.
- PostgreSQL builds an index for every PRIMARY KEY and UNIQUE rule, so they are fast to check. It does not index the column that holds a foreign key (author_id); you usually add that index yourself.
- SQLite checks foreign keys only after PRAGMA foreign_keys = ON, which each connection must run. Without it, author 9 would be stored.
- The Python example uses the sqlite3 module that ships with Python. It prints: refused: FOREIGN KEY constraint failed, then stored (4, 'Stardust', 'B2', 1999, 2).
- PostgreSQL documentation: Constraints
- PostgreSQL documentation: CREATE TABLE
- PostgreSQL documentation: Unique Indexes
- SQLite: SQLite Foreign Key Support
- SQLite: CREATE TABLE
- Python Library Reference: sqlite3
- E. F. Codd, A Relational Model of Data for Large Shared Data Banks (CACM, 1970)
- Silberschatz, Korth and Sudarshan, Database System Concepts (7th ed.), chapters 2 and 4
Quiz Unlocks after the last step.