Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 90 ยท Schema Design and Constraints

Constraints in Depth

Master PostgreSQL constraints: NOT NULL, CHECK, UNIQUE with NULLS NOT DISTINCT, primary and foreign keys, ON DELETE actions, DEFERRABLE and NOT VALID.

A constraint is a rule that the database enforces for you, every time, no matter which application or script writes the data. Application code has bugs, people run one-off scripts, and a second service eventually shows up. A constraint is the one check that cannot be skipped. PostgreSQL has a particularly complete set of them, and some options, like DEFERRABLE and NOT VALID, solve real production problems.

If you need the basics first, read Constraints and Foreign keys. This lesson goes deeper into how each kind behaves in PostgreSQL, including NULL handling, what happens when a parent row is deleted, and how to add a constraint to a huge table without blocking it.

What runs live here. The editors use SQLite (sql.js), which supports NOT NULL, CHECK, UNIQUE, primary keys, foreign keys with actions and deferred checking. The commands that exist only in PostgreSQL (NULLS NOT DISTINCT, NOT VALID, VALIDATE CONSTRAINT, SET CONSTRAINTS) are read-only blocks.

Five kinds of rule

NOT NULL A value must exist.
CHECK A condition must be true.
UNIQUE No two rows may match.
PRIMARY KEY Unique, not null, the row's identity.
1 / 2

NOT NULL and CHECK

NOT NULL rejects missing values. Add it to every column where "unknown" is not a real state. It is cheap, and it makes later queries simpler, because you never have to wonder about NULL.

A CHECK constraint holds a boolean expression. A row is accepted when the expression is true or NULL. This catches people out: CHECK (price > 0) allows a row whose price is NULL, because NULL > 0 is unknown, not false. If the column must also exist, pair the check with NOT NULL.

SQL NOT NULL, CHECK and UNIQUE together

Press Run (or Ctrl + Enter) to see the result here.

The three rows are accepted, including the one with a NULL discount. Notice the two kinds of check. The first is a column constraint written next to one column. The last one, CHECK (discount IS NULL OR discount <= 50), is a table constraint that can look at several columns. PostgreSQL treats them the same, and it names them automatically (products_price_check, products_check).

Keep checks simple and immutable. They may not contain a subquery to another table, and an expression that changes over time, such as now(), makes old rows invalid without anyone noticing. Rules that need other rows belong in a foreign key, an exclusion constraint or a trigger.

UNIQUE and NULLs

A UNIQUE constraint guarantees no two rows have the same value in the column or columns. PostgreSQL builds a unique index to enforce it. By default NULL values never conflict, so any number of rows can have NULL in a unique column. This matches SQLite, as the live example shows:

SQL Several NULLs in a UNIQUE column

Press Run (or Ctrl + Enter) to see the result here.

Four rows are stored and only one has an email. Three rows with NULL do not clash, because two unknown values are not considered equal. That is often what you want ("email is optional but unique when present"). If you want only one NULL allowed, PostgreSQL 15 added a clause for it. It is read-only here:

SQL
CREATE TABLE members (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text,
  UNIQUE NULLS NOT DISTINCT (email)
);
-- A second row with email NULL now fails with a unique violation

NULLS NOT DISTINCT treats NULLs as equal for this constraint. It also helps with multicolumn keys where a NULL in one part used to make the constraint useless.

A primary key is UNIQUE plus NOT NULL, and a table can have only one. A table may have many UNIQUE constraints. When another table references your table, the target must be a primary key or a unique constraint.

Foreign keys and their actions

A foreign key says "this value must exist in that other table". The interesting part is what happens when someone deletes or changes the parent row. You choose with ON DELETE and ON UPDATE:

Action Effect when the parent row is deleted
NO ACTION (default) Raise an error if children still exist. The check can be deferred.
RESTRICT Raise an error immediately. It cannot be deferred.
CASCADE Delete the child rows too.
SET NULL Set the child's column to NULL.
SET DEFAULT Set the child's column to its default value.

Pick the action to match the meaning of the data. Order items cannot exist without their order, so CASCADE. A book's editor_id can safely become unknown if the editor leaves, so SET NULL. Invoices should block the deletion of a customer, so RESTRICT or the default.

SQL CASCADE and SET NULL in action

Press Run (or Ctrl + Enter) to see the result here.

Deleting author 1 removes books 1 and 2. Deleting editor 1 leaves book 3 in place, with editor_id set to NULL. Only Paper Moons remains.

PostgreSQL 15 added a way to limit SET NULL and SET DEFAULT to specific columns. This matters for composite foreign keys, where you may not want to null a column that is also part of another key:

SQL
FOREIGN KEY (tenant_id, editor_id) REFERENCES editors (tenant_id, id)
  ON DELETE SET NULL (editor_id)

Two habits are worth building. First, PostgreSQL does not index the referencing column automatically. Deleting a parent row has to find its children, so a foreign key column without an index makes deletes and joins slow on large tables. Create the index yourself. Second, think carefully before using CASCADE on tables that hold important history, because one delete can quietly remove thousands of rows.

DEFERRABLE constraints

Normally a constraint is checked at the end of each statement. Sometimes you need to be temporarily inconsistent inside a transaction, for example when inserting two rows that reference each other, or when swapping unique values between two rows. A DEFERRABLE constraint can wait until COMMIT:

SQL
CREATE TABLE employees (
  id         bigint PRIMARY KEY,
  manager_id bigint REFERENCES employees (id)
             DEFERRABLE INITIALLY IMMEDIATE
);

BEGIN;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO employees VALUES (1, 2);  -- manager 2 does not exist yet
INSERT INTO employees VALUES (2, 1);
COMMIT;                               -- both rows are checked here: OK

INITIALLY IMMEDIATE is the default behavior, checked per statement, until you ask otherwise with SET CONSTRAINTS. INITIALLY DEFERRED checks at commit from the start. In PostgreSQL, deferral works for UNIQUE, PRIMARY KEY, EXCLUDE and foreign keys, but not for CHECK or NOT NULL. The live version below uses SQLite's DEFERRABLE INITIALLY DEFERRED:

SQL Insert the child before the parent

Press Run (or Ctrl + Enter) to see the result here.

The result is one row: Kit with parent Rowan. Without DEFERRABLE, the first insert would have failed immediately.

Adding constraints to a big table: NOT VALID

On a table with hundreds of millions of rows, ALTER TABLE ... ADD CONSTRAINT normally scans every row while holding a strong lock, which can freeze writes for minutes. PostgreSQL lets you split the work in two:

SQL
-- 1. Add the rule. New and updated rows are checked immediately. Old rows are not.
ALTER TABLE orders
  ADD CONSTRAINT orders_total_check CHECK (total >= 0) NOT VALID;

-- 2. Check the existing rows later. This allows normal reads and writes while it scans.
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_check;

The same pattern works for foreign keys:

SQL
ALTER TABLE order_items
  ADD CONSTRAINT order_items_order_fk
  FOREIGN KEY (order_id) REFERENCES orders (id) NOT VALID;

ALTER TABLE order_items VALIDATE CONSTRAINT order_items_order_fk;

Step 1 needs only a brief lock. Step 2 runs with a weaker lock that does not block ordinary inserts, updates and deletes. If old data breaks the rule, VALIDATE fails and you fix those rows first. You will see this often in Schema Changes and Migrations.

To make a large existing column NOT NULL without a long scan, add CHECK (col IS NOT NULL) NOT VALID, validate it, then run ALTER COLUMN col SET NOT NULL. PostgreSQL notices the validated check and skips the scan, and you can drop the check afterwards.

A constraint declared NOT VALID is still enforced for new data. It is only "unvalidated" for rows that already existed. The planner will not use an unvalidated constraint to optimize queries until it is validated.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A column has CHECK (price > 0) but no NOT NULL. Is a row with price NULL accepted?

    A CHECK passes unless it evaluates to false. NULL > 0 is unknown, so the row is accepted. Add NOT NULL if the value is required.
  2. 2. What does UNIQUE NULLS NOT DISTINCT (email) change in PostgreSQL 15 and later?

    By default any number of NULLs can coexist in a unique column. With NULLS NOT DISTINCT, a second NULL violates the constraint.
  3. 3. Which ON DELETE action removes the child rows when the parent row is deleted?

    CASCADE deletes dependent rows automatically. SET NULL keeps them and clears the column, while RESTRICT and NO ACTION block the delete.
  4. 4. Why use ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT on a huge table?

    NOT VALID adds the rule for new rows with a short lock. VALIDATE then scans old rows with a weaker lock that still allows normal reads and writes.
  5. 5. In PostgreSQL, which constraint types can be DEFERRABLE?

    Uniqueness, exclusion and referential checks can wait until commit. CHECK and NOT NULL are always checked immediately.

Summary

  • NOT NULL and CHECK keep bad values out; a CHECK passes when its result is true or NULL, so combine it with NOT NULL when needed.
  • UNIQUE allows many NULLs by default; NULLS NOT DISTINCT (PostgreSQL 15+) allows only one.
  • A primary key is unique plus not null, one per table.
  • Foreign key actions are NO ACTION, RESTRICT, CASCADE, SET NULL and SET DEFAULT. Index referencing columns yourself.
  • DEFERRABLE constraints can wait for COMMIT; useful for circular references and swaps.
  • NOT VALID then VALIDATE CONSTRAINT adds rules to big tables without long blocking locks.
  • The live editors ran the portable rules on SQLite; PostgreSQL-only syntax was shown read-only.

Next: Exclusion Constraints and Temporal Design.