Constraints & data integrity

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 NULLs fails, so you would have to fill them in first:

ALTER TABLE customers ALTER COLUMN city SET NOT NULL;

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:

ERROR:  duplicate key value violates unique constraint "customers_email_key"
DETAIL:  Key (email)=([email protected]) 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, NULLs are not equal to each other, so a UNIQUE column allows any number of NULLs. If you want at most one NULL, declare UNIQUE NULLS NOT DISTINCT (available since PostgreSQL 15). Second, uniqueness is exact: [email protected] and [email protected] are different strings. For case-insensitive uniqueness, create a unique index on an expression:

CREATE UNIQUE INDEX customers_email_lower_key ON customers (lower(email));

INSERT INTO customers (name, email, joined_on)
VALUES ('Ada Again', '[email protected]', '2025-04-01');

ERROR:  duplicate key value violates unique constraint "customers_email_lower_key"
DETAIL:  Key (lower(email))=([email protected]) 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):

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:

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:

DELETE FROM orders WHERE order_id = 4;
SELECT count(*) FROM order_items WHERE order_id = 4;

DELETE 1
 count
-------
     0
(1 row)

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:

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:

UPDATE books SET stock = stock - 1 WHERE book_id = 3;

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).

UPDATE orders SET status = 'shiped' WHERE order_id = 7;

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:

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.

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:

ALTER TABLE books ADD CONSTRAINT books_must_have_stock CHECK (stock > 0);

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:

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:

UPDATE customers
SET email = CASE customer_id WHEN 1 THEN '[email protected]'
                             WHEN 2 THEN '[email protected]' END
WHERE customer_id IN (1, 2);

ERROR:  duplicate key value violates unique constraint "customers_email_key"
DETAIL:  Key (email)=([email protected]) 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):

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)');

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.

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);

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".

#Key takeaways

• Constraints are enforced for every writer and every race, which application validation alone cannot guarantee.
• Default to NOT NULL; UNIQUE allows many NULLs 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 NULLs 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.