# Constraints & data integrity > Use NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and exclusion constraints so the database itself rejects invalid data. SQL & Databases roadmap · Design & performance · 17 min read · https://levelcode.org/roadmaps/sql/constraints A schema describes the shape of your data; constraints describe the rules. "Every order belongs to a real customer", "stock is never negative", "one account per email" — if those rules live only in application code, they will eventually be broken by a bug, a second service, a batch script or two requests racing each other. Constraints make the database enforce them for every writer, every time. This lesson covers each kind of constraint, the choices you make with foreign keys, and how to add constraints to tables that already hold data. ## Why the database should enforce rules Application validation is still useful — it gives friendly error messages early. But it cannot be the only line of defence: - **There is rarely only one writer.** Admin tools, import scripts, other services and engineers running SQL by hand all bypass your app's validation. - **Check-then-write is racy.** Two sign-ups with the same email can both check "is this email taken?", both see "no", and both insert. A unique constraint makes one of them fail, no matter the timing. - **Bad data is expensive.** Once a negative stock level or an order for a nonexistent customer is stored, every report and every piece of code that reads it must cope with it. A constraint violation aborts the statement with an error, and nothing from that statement is stored. Your bookstore already has several constraints from the first lesson: primary keys, foreign keys, `NOT NULL` and `UNIQUE (email)`. This lesson adds the rest. ## NOT NULL, UNIQUE and PRIMARY KEY ### NOT NULL `NOT NULL` is the simplest and most valuable constraint: the column must always have a value. Make it your default and allow `NULL` only when "unknown" or "not applicable" is a genuine state — as with `customers.city`. Trying to add `NOT NULL` to a column that already contains `NULL`s fails, so you would have to fill them in first: ```sql ALTER TABLE customers ALTER COLUMN city SET NOT NULL; ``` ```text ERROR: column "city" of relation "customers" contains null values ``` ### UNIQUE A `UNIQUE` constraint guarantees no two rows share the same value in the given column or columns. PostgreSQL implements it with a unique B-tree index (you will learn about indexes in the next lesson), and the error names the constraint: ```text ERROR: duplicate key value violates unique constraint "customers_email_key" DETAIL: Key (email)=(ada@example.com) already exists. ``` A multi-column constraint like `UNIQUE (author_id, title)` means the *combination* must be unique: two authors can each have a book called *Collected Stories*, but one author cannot have it twice. Two subtleties catch people out. First, **`NULL`s are not equal to each other**, so a `UNIQUE` column allows any number of `NULL`s. If you want at most one `NULL`, declare `UNIQUE NULLS NOT DISTINCT` (available since PostgreSQL 15). Second, uniqueness is **exact**: `ADA@example.com` and `ada@example.com` are different strings. For case-insensitive uniqueness, create a unique index on an expression: ```sql CREATE UNIQUE INDEX customers_email_lower_key ON customers (lower(email)); INSERT INTO customers (name, email, joined_on) VALUES ('Ada Again', 'ADA@example.com', '2025-04-01'); ``` ```text ERROR: duplicate key value violates unique constraint "customers_email_lower_key" DETAIL: Key (lower(email))=(ada@example.com) already exists. ``` ### PRIMARY KEY A primary key is `UNIQUE` plus `NOT NULL`, with the extra meaning "this is the row's identity". A table can have only one, and foreign keys reference it by default. Every table should have one, even junction tables, where it is usually the composite of the two foreign keys. ## FOREIGN KEY: referential integrity A foreign key requires every value in the referencing column to exist in the referenced key (or be `NULL`). You have seen the insert-side error. The interesting question is what happens on the *other* side — when someone deletes or changes a referenced row. You choose with `ON DELETE` (and, less often, `ON UPDATE`): | Action | When the referenced row is deleted | Good for | | --- | --- | --- | | `NO ACTION` (default) | error, checked at the end of the statement | most relationships | | `RESTRICT` | error, checked immediately | the same, without allowing deferral | | `CASCADE` | referencing rows are deleted too | parts that cannot exist without their whole | | `SET NULL` | referencing column is set to `NULL` | optional links | | `SET DEFAULT` | referencing column is set to its default | rare | Apply that to the bookstore: - `order_items.order_id` → `orders`: a line item is part of its order and meaningless without it. **CASCADE**. - `orders.customer_id` → `customers`: deleting a customer must not silently delete their purchase history. Keep the default **NO ACTION**, so the delete fails until you decide what to do. - `customers.referred_by` → `customers`: if a referrer leaves, the people they referred should stay. **SET NULL**. Changing an existing foreign key means dropping it and adding it back with the new action. Both steps can go in one `ALTER TABLE`: ```sql ALTER TABLE order_items DROP CONSTRAINT order_items_order_id_fkey, ADD CONSTRAINT order_items_order_id_fkey FOREIGN KEY (order_id) REFERENCES orders (order_id) ON DELETE CASCADE; ALTER TABLE customers DROP CONSTRAINT customers_referred_by_fkey, ADD CONSTRAINT customers_referred_by_fkey FOREIGN KEY (referred_by) REFERENCES customers (customer_id) ON DELETE SET NULL; ``` Now deleting the cancelled order also deletes its line, in one statement: ```sql DELETE FROM orders WHERE order_id = 4; SELECT count(*) FROM order_items WHERE order_id = 4; ``` ```text DELETE 1 count ------- 0 (1 row) ``` > [!WARNING] > `CASCADE` is powerful in both directions. A cascade can chain through several tables, so one innocent-looking `DELETE` may remove thousands of rows. Use it only for true part-of relationships, and prefer soft deletes (a `deleted_at` column) for business records you may need later. One performance note: PostgreSQL automatically indexes primary keys and unique constraints, but **not** foreign key columns. Every delete from `orders` has to find matching `order_items` rows, so on large tables you normally add an index on each foreign key column yourself. The next lesson shows why. ## CHECK constraints A `CHECK` constraint is a boolean expression every row must satisfy. It encodes business rules that types alone cannot. Add the rules the bookstore has been relying on: ```sql ALTER TABLE books ADD CONSTRAINT books_price_positive CHECK (price > 0), ADD CONSTRAINT books_stock_nonnegative CHECK (stock >= 0); ALTER TABLE order_items ADD CONSTRAINT order_items_quantity_positive CHECK (quantity > 0); ALTER TABLE orders ADD CONSTRAINT orders_status_valid CHECK (status IN ('paid', 'shipped', 'cancelled')); ALTER TABLE customers ADD CONSTRAINT customers_not_self_referred CHECK (referred_by <> customer_id); ``` Now overselling a sold-out book and typos in statuses are impossible: ```sql UPDATE books SET stock = stock - 1 WHERE book_id = 3; ``` ```text ERROR: new row for relation "books" violates check constraint "books_stock_nonnegative" DETAIL: Failing row contains (3, The Dispossessed, 1, sci-fi, 16.00, 1974, -1). ``` ```sql UPDATE orders SET status = 'shiped' WHERE order_id = 7; ``` ```text ERROR: new row for relation "orders" violates check constraint "orders_status_valid" DETAIL: Failing row contains (7, 5, 2025-03-08, shiped). ``` The stock check is the database-level guarantee behind every "only sell what you have" rule. Combined with an `UPDATE ... SET stock = stock - n`, it stays correct even when two customers buy the last copy at the same moment: one update succeeds and the other fails. ### CHECK and NULL A `CHECK` constraint rejects a row only when the expression is **false**. When it is unknown — because a column is `NULL` — the row is accepted: ```sql ALTER TABLE books ADD CONSTRAINT books_published_plausible CHECK (published BETWEEN 1450 AND 2100); INSERT INTO books (title, author_id, genre, price, published) VALUES ('Untitled draft', 5, 'fantasy', 9.99, NULL); -- succeeds ``` This is usually what you want (an unknown year is not an implausible year), and it is why `customers_not_self_referred` does not reject customers with no referrer. If a value must also be present, add `NOT NULL` as well. > [!IMPORTANT] > Name your constraints (`CONSTRAINT books_price_positive CHECK (...)`). PostgreSQL generates names such as `books_price_check` if you do not, but explicit names make error messages self-explanatory and let application code map a violation to a helpful message. A `CHECK` can only see the row being written. It cannot query other tables, so "a review must be written after the customer joined" or "an order must have at least one item" cannot be `CHECK` constraints; they need triggers or application logic, ideally inside a transaction. ## Advanced constraints ### Adding constraints to large tables `ALTER TABLE ... ADD CONSTRAINT` validates every existing row, and fails if any violate the rule: ```sql ALTER TABLE books ADD CONSTRAINT books_must_have_stock CHECK (stock > 0); ``` ```text ERROR: check constraint "books_must_have_stock" of relation "books" is violated by some row ``` On a large, busy table, that validation scan holds a lock that blocks writes for its whole duration. The safe pattern is two steps: add the constraint as `NOT VALID`, which enforces it for new writes immediately but skips existing rows, then validate separately with a weaker lock that lets normal traffic continue: ```sql ALTER TABLE order_items ADD CONSTRAINT order_items_unit_price_positive CHECK (unit_price > 0) NOT VALID; ALTER TABLE order_items VALIDATE CONSTRAINT order_items_unit_price_positive; ``` This works for `CHECK` and foreign key constraints. Fix any violating rows between the two steps. ### Deferrable constraints By default, PostgreSQL checks a non-deferrable `UNIQUE` or primary key constraint *row by row* as a statement runs. That makes swapping two values fail, even though the end result is valid: ```sql UPDATE customers SET email = CASE customer_id WHEN 1 THEN 'ben@example.com' WHEN 2 THEN 'ada@example.com' END WHERE customer_id IN (1, 2); ``` ```text ERROR: duplicate key value violates unique constraint "customers_email_key" DETAIL: Key (email)=(ben@example.com) already exists. ``` Declaring the constraint `DEFERRABLE` changes this. A deferrable constraint that is `INITIALLY IMMEDIATE` is checked at the end of each statement, so the swap above succeeds. Inside a transaction you can go further with `SET CONSTRAINTS customers_email_key DEFERRED;`, which postpones the check until `COMMIT` — useful when a sequence of statements is temporarily inconsistent, such as two rows that reference each other. Foreign keys can be deferrable too. The cost is that deferrable unique constraints cannot be used as `ON CONFLICT` targets, so use them only where needed. ### Exclusion constraints An exclusion constraint generalises `UNIQUE`: no two rows may satisfy a given set of comparisons. Its classic use is preventing overlapping ranges. Here, a book cannot have two promotions running at the same time (the `btree_gist` extension, shipped with PostgreSQL, lets the `=` comparison take part in a GiST index): ```sql CREATE EXTENSION IF NOT EXISTS btree_gist; CREATE TABLE promotions ( book_id integer NOT NULL REFERENCES books (book_id), discount numeric(3,2) NOT NULL CHECK (discount > 0 AND discount < 1), during daterange NOT NULL, EXCLUDE USING gist (book_id WITH =, during WITH &&) ); INSERT INTO promotions VALUES (4, 0.20, '[2025-04-01,2025-04-15)'); INSERT INTO promotions VALUES (4, 0.10, '[2025-04-10,2025-04-20)'); ``` ```text ERROR: conflicting key value violates exclusion constraint "promotions_book_id_during_excl" DETAIL: Key (book_id, during)=(4, [2025-04-10,2025-04-20)) conflicts with existing key (book_id, during)=(4, [2025-04-01,2025-04-15)). ``` `&&` means "overlaps". The same promotion for book 5 would be accepted. Room bookings, shift rotas and price validity periods are all natural fits. ## Practice Reload the bookstore script. Create the `reviews` table from the first lesson's practice, this time with full constraints: - One review per customer per book. - Reviews disappear if the customer's account or the book is deleted. - `rating` is required and must be between 1 and 5. - `comment` is optional, but at most 2,000 characters. - `written_on` defaults to today. Then write five inserts: one valid review with a comment, one valid review without, and three that should fail — a rating of 6, a duplicate review, and a review of book 99. Predict each error before running it. Finally, decide whether "a customer can only review a book they have bought" can be a `CHECK` constraint. **Solution** ```sql CREATE TABLE reviews ( customer_id integer NOT NULL REFERENCES customers (customer_id) ON DELETE CASCADE, book_id integer NOT NULL REFERENCES books (book_id) ON DELETE CASCADE, rating integer NOT NULL CONSTRAINT reviews_rating_range CHECK (rating BETWEEN 1 AND 5), comment text CONSTRAINT reviews_comment_length CHECK (length(comment) <= 2000), written_on date NOT NULL DEFAULT current_date, PRIMARY KEY (customer_id, book_id) ); INSERT INTO reviews (customer_id, book_id, rating, comment) VALUES (1, 1, 5, 'A classic.'); -- ok INSERT INTO reviews (customer_id, book_id, rating) VALUES (2, 9, 4); -- ok, comment is NULL INSERT INTO reviews (customer_id, book_id, rating, written_on) VALUES (1, 4, 6, '2025-04-01'); INSERT INTO reviews (customer_id, book_id, rating) VALUES (1, 1, 3); INSERT INTO reviews (customer_id, book_id, rating) VALUES (1, 99, 3); ``` ```text ERROR: new row for relation "reviews" violates check constraint "reviews_rating_range" DETAIL: Failing row contains (1, 4, 6, null, 2025-04-01). ERROR: duplicate key value violates unique constraint "reviews_pkey" DETAIL: Key (customer_id, book_id)=(1, 1) already exists. ERROR: insert or update on table "reviews" violates foreign key constraint "reviews_book_id_fkey" DETAIL: Key (book_id)=(99) is not present in table "books". ``` The comment check passes for the second review because `length(NULL) <= 2000` is unknown, not false. `ON DELETE CASCADE` fits here because a review is owned by both its customer and its book. "Only review books you have bought" depends on other tables (`orders` and `order_items`), and a `CHECK` constraint can only see the row being written, so it cannot be a `CHECK`. You would enforce it in application code inside a transaction, or with a trigger. Drop the table when you are done: `DROP TABLE reviews;`. ## Key takeaways - Constraints are enforced for every writer and every race, which application validation alone cannot guarantee. - Default to `NOT NULL`; `UNIQUE` allows many `NULL`s unless you declare `NULLS NOT DISTINCT`; use a unique expression index for case-insensitive uniqueness. - Choose each foreign key's `ON DELETE` action deliberately: `CASCADE` for parts of a whole, `SET NULL` for optional links, the default to protect history. Index foreign key columns yourself. - `CHECK` rejects rows only when the expression is false, so `NULL`s pass; it can only see the current row. - Add constraints to big tables with `NOT VALID` then `VALIDATE CONSTRAINT`; use `DEFERRABLE` for temporarily inconsistent changes and exclusion constraints to forbid overlaps. ## Quiz 1. books has CHECK (published BETWEEN 1450 AND 2100). What happens when you insert a book with published = NULL? A. It fails with a check constraint violation B. PostgreSQL stores 1450 instead C. It fails with a NOT NULL violation D. It succeeds, because the check evaluates to unknown rather than false **Answer: D.** CHECK constraints reject a row only when the expression is false. NULL BETWEEN 1450 AND 2100 is unknown, so the row is accepted. Add NOT NULL if the value is required. 2. A column has a plain UNIQUE constraint. Can two rows both store NULL in it? A. Yes — NULLs are not considered equal, unless the constraint is declared NULLS NOT DISTINCT B. No — UNIQUE always rejects duplicate NULLs C. Only if the column is also part of the primary key D. Only inside a deferred transaction **Answer: A.** By default NULLs are distinct for uniqueness purposes, so any number are allowed. PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT to allow at most one NULL. 3. Which ON DELETE action fits order_items.order_id, which references orders? A. SET NULL B. NO ACTION C. CASCADE D. SET DEFAULT **Answer: C.** An order line is part of its order and meaningless on its own, so deleting the order should delete its lines. SET NULL would leave orphaned lines (and order_id is NOT NULL anyway); NO ACTION would block the delete. 4. You need to add a CHECK constraint to a 500-million-row table without blocking writes for the length of a full scan. What is the standard approach? A. Add it as DEFERRABLE INITIALLY DEFERRED B. Add it with NOT VALID, then run ALTER TABLE ... VALIDATE CONSTRAINT separately C. Create a unique index first D. Add it inside a transaction and roll back if it is slow **Answer: B.** NOT VALID enforces the rule for new writes immediately without scanning existing rows. VALIDATE CONSTRAINT then checks old rows while holding a weaker lock that does not block normal reads and writes. CHECK constraints cannot be deferrable.