9.2 Constraints: Your Data's Immune System — The Complete SQL Course

Constraints are table rules PostgreSQL checks every time data is written. They turn your schema from a drawing into an immune system: bad rows are refused at the door, no matter whether they come from your app, a script, or a tired human at sql>.

You already created tables in 9.1. Now you attach promises to them: identity, required values, relationships, uniqueness, and business rules.

Executable documentation

A constraint is an enforced rule on a table — documentation the database executes, not prose someone can forget. The receiving-checklist idea from 9.1 gets teeth: if a delivery is missing a required fact, points at a customer that does not exist, or asks for an impossible quantity, PostgreSQL rejects it before the bad row lands.

Here is a tiny cafe ordering schema with the main constraint families in one place:

CREATE TABLE cafe_customers (
  customer_id integer PRIMARY KEY,
  email text NOT NULL UNIQUE,
  display_name text NOT NULL
);
 
CREATE TABLE pickup_slots (
  slot_id integer PRIMARY KEY,
  slot_day date NOT NULL,
  slot_label text NOT NULL,
  CONSTRAINT pickup_slots_day_label_key
    UNIQUE (slot_day, slot_label)
    DEFERRABLE INITIALLY IMMEDIATE
);
 
CREATE TABLE drink_orders (
  order_id integer PRIMARY KEY,
  customer_id integer NOT NULL
    REFERENCES cafe_customers(customer_id)
    ON DELETE RESTRICT,
  slot_id integer
    REFERENCES pickup_slots(slot_id)
    ON DELETE SET NULL,
  drink_name text NOT NULL,
  quantity integer NOT NULL CHECK (quantity BETWEEN 1 AND 12),
  status text NOT NULL DEFAULT 'queued'
    CHECK (status IN ('queued', 'paid', 'canceled'))
);
 
CREATE TABLE order_notes (
  note_id integer PRIMARY KEY,
  order_id integer NOT NULL
    REFERENCES drink_orders(order_id)
    ON DELETE CASCADE,
  note_text text NOT NULL
);
 
INSERT INTO cafe_customers (customer_id, email, display_name)
VALUES
  (1, 'ada@cafe.test', 'Ada'),
  (2, 'ken@cafe.test', 'Ken');
 
INSERT INTO pickup_slots (slot_id, slot_day, slot_label)
VALUES
  (10, DATE '2026-05-12', '08:00'),
  (11, DATE '2026-05-12', '08:15');
 
INSERT INTO drink_orders (order_id, customer_id, slot_id, drink_name, quantity)
VALUES
  (100, 1, 10, 'Latte', 2),
  (101, 2, 11, 'Sencha', 1);
 
INSERT INTO order_notes (note_id, order_id, note_text)
VALUES (900, 100, 'No cinnamon');
 
SELECT o.order_id,
       c.display_name,
       ps.slot_label,
       o.drink_name,
       o.quantity
FROM drink_orders AS o
JOIN cafe_customers AS c
  ON c.customer_id = o.customer_id
LEFT JOIN pickup_slots AS ps
  ON ps.slot_id = o.slot_id
ORDER BY o.order_id;

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 2
INSERT 0 1
 order_id | display_name | slot_label | drink_name | quantity
----------+--------------+------------+------------+----------
      100 | Ada          | 08:00      | Latte      |        2
      101 | Ken          | 08:15      | Sencha     |        1
(2 rows)

PRIMARY KEY: one row, one identity

A primary key is a column or column group that identifies one row and refuses both duplicates and NULL. You saw the idea in the relationship lessons; now you can write it into the table.

customer_id integer PRIMARY KEY means customer 1 is exactly one customer. PostgreSQL also creates the uniqueness machinery needed to check that promise. Lesson 9.3 is where you choose whether that key should be a natural value, an identity number, or a UUID; for today, hand-written small integers keep the rule visible.

NOT NULL is still a constraint too. If a required value is missing, PostgreSQL stops the insert with SQLSTATE 23502:

INSERT INTO cafe_customers (customer_id, email)
VALUES (3, 'rosa@cafe.test');

ERROR:  null value in column "display_name" of relation "cafe_customers" violates not-null constraint
DETAIL:  Failing row contains (3, rosa@cafe.test, null).

UNIQUE: no duplicate facts

A unique constraint says duplicate non-NULL values are not allowed. In this lesson's examples the unique columns are also NOT NULL, so each value, or each selected combination of values, may appear only once. PostgreSQL treats NULLs as distinct by default, so nullable unique columns need extra care.

INSERT INTO cafe_customers (customer_id, email, display_name)
VALUES (3, 'ada@cafe.test', 'Other Ada');

ERROR:  duplicate key value violates unique constraint "cafe_customers_email_key"
DETAIL:  Key (email)=(ada@cafe.test) already exists.

The pickup slot rule is multi-column: UNIQUE (slot_day, slot_label). It does not say '08:00' can happen only once forever; it says the pair (day, label) cannot repeat:

INSERT INTO pickup_slots (slot_id, slot_day, slot_label)
VALUES (12, DATE '2026-05-12', '08:00');

ERROR:  duplicate key value violates unique constraint "pickup_slots_day_label_key"
DETAIL:  Key (slot_day, slot_label)=(2026-05-12, 08:00) already exists.

That is the difference between "no two customers share an email" and "no two pickup slots share the same day-and-label pair." The grain of the rule has to match the sentence you would say out loud.

FOREIGN KEY: relationships are values, protected

A foreign key is a stored value that must match a key in another table. This is the old course rule — relationships are values, not wires — with enforcement turned on.

drink_orders.customer_id REFERENCES cafe_customers(customer_id) means an order cannot point at customer 99 unless customer 99 exists:

INSERT INTO drink_orders (order_id, customer_id, slot_id, drink_name, quantity)
VALUES (102, 99, 10, 'Flat White', 1);

ERROR:  insert or update on table "drink_orders" violates foreign key constraint "drink_orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(99) is not present in table "cafe_customers".

Foreign keys also decide what happens when a referenced row is deleted. ON DELETE RESTRICT is the conservative choice: do not delete the parent while child rows still point at it. Rehearse the target first:

SELECT c.customer_id,
       c.display_name,
       o.order_id
FROM cafe_customers AS c
JOIN drink_orders AS o
  ON o.customer_id = c.customer_id
WHERE c.customer_id = 1
ORDER BY o.order_id;

 customer_id | display_name | order_id
-------------+--------------+----------
           1 | Ada          |      100
(1 row)

Then PostgreSQL refuses the delete:

DELETE FROM cafe_customers
WHERE customer_id = 1;

ERROR:  update or delete on table "cafe_customers" violates RESTRICT setting of foreign key constraint "drink_orders_customer_id_fkey" on table "drink_orders"
DETAIL:  Key (customer_id)=(1) is referenced from table "drink_orders".

ON DELETE CASCADE says deleting the parent deletes dependent rows too. That is powerful, so rehearse:

SELECT o.order_id,
       n.note_id,
       n.note_text
FROM drink_orders AS o
JOIN order_notes AS n
  ON n.order_id = o.order_id
WHERE o.order_id = 100
ORDER BY n.note_id;
 
DELETE FROM drink_orders
WHERE order_id = 100;
 
SELECT count(*) AS remaining_notes
FROM order_notes
WHERE order_id = 100;

 order_id | note_id |  note_text
----------+---------+-------------
      100 |     900 | No cinnamon
(1 row)
DELETE 1
 remaining_notes
-----------------
               0
(1 row)

ON DELETE SET NULL keeps the child row and clears the reference. That only works because slot_id allows NULL:

SELECT ps.slot_id,
       ps.slot_label,
       o.order_id
FROM pickup_slots AS ps
JOIN drink_orders AS o
  ON o.slot_id = ps.slot_id
WHERE ps.slot_id = 11
ORDER BY o.order_id;
 
DELETE FROM pickup_slots
WHERE slot_id = 11;
 
SELECT order_id, slot_id, drink_name
FROM drink_orders
ORDER BY order_id;

 slot_id | slot_label | order_id
---------+------------+----------
      11 | 08:15      |      101
(1 row)
DELETE 1
 order_id | slot_id | drink_name
----------+---------+------------
      101 |    NULL | Sencha
(1 row)

Choose the action by the business meaning. Restrict when deleting the parent would make a child dishonest. Cascade when the child has no meaning without the parent. Set null when the relationship is optional and the child still makes sense.

CHECK: business rules in the table

A check constraint is a boolean rule PostgreSQL tests for each row. PostgreSQL rejects the row when the check is FALSE; if missing values should be rejected too, pair CHECK with NOT NULL, as this quantity column does.

INSERT INTO drink_orders (order_id, customer_id, drink_name, quantity)
VALUES (103, 2, 'Batch Brew', 0);

ERROR:  new row for relation "drink_orders" violates check constraint "drink_orders_quantity_check"
DETAIL:  Failing row contains (103, 2, null, Batch Brew, 0, queued).

quantity integer NOT NULL CHECK (quantity BETWEEN 1 AND 12) says the rule where every writer can see it, and where PostgreSQL can enforce it against real integers. It is not a replacement for friendly form validation; it is the backstop when friendly validation is skipped.

Deferrable constraints: a peek

A deferrable constraint is a constraint PostgreSQL can check at the end of a transaction instead of immediately, when you explicitly defer it. The slot uniqueness rule used DEFERRABLE INITIALLY IMMEDIATE, which means it behaves normally unless a transaction says otherwise.

You do not need this often. The classic use is a swap: two rows temporarily collide on a unique position while you reorder them, then end valid. Transactions get their proper treatment in Section 12, and migrations return to careful constraint timing later. For now, recognize the phrase and keep the default mental model: constraints are checked immediately.

sql — playgroundlive

CREATE TABLE cafe_customers (
customer_id integer PRIMARY KEY,
email text NOT NULL UNIQUE,
display_name text NOT NULL
);

CREATE TABLE pickup_slots (
slot_id integer PRIMARY KEY,
slot_day date NOT NULL,
slot_label text NOT NULL,
CONSTRAINT pickup_slots_day_label_key
  UNIQUE (slot_day, slot_label)
  DEFERRABLE INITIALLY IMMEDIATE
);

CREATE TABLE drink_orders (
order_id integer PRIMARY KEY,
customer_id integer NOT NULL
  REFERENCES cafe_customers(customer_id)
  ON DELETE RESTRICT,
slot_id integer
  REFERENCES pickup_slots(slot_id)
  ON DELETE SET NULL,
drink_name text NOT NULL,
quantity integer NOT NULL CHECK (quantity BETWEEN 1 AND 12),
status text NOT NULL DEFAULT 'queued'
  CHECK (status IN ('queued', 'paid', 'canceled'))
);

CREATE TABLE order_notes (
note_id integer PRIMARY KEY,
order_id integer NOT NULL
  REFERENCES drink_orders(order_id)
  ON DELETE CASCADE,
note_text text NOT NULL
);

INSERT INTO cafe_customers (customer_id, email, display_name)
VALUES
(1, 'ada@cafe.test', 'Ada'),
(2, 'ken@cafe.test', 'Ken');

INSERT INTO pickup_slots (slot_id, slot_day, slot_label)
VALUES
(10, DATE '2026-05-12', '08:00'),
(11, DATE '2026-05-12', '08:15');

INSERT INTO drink_orders (order_id, customer_id, slot_id, drink_name, quantity)
VALUES
(100, 1, 10, 'Latte', 2),
(101, 2, 11, 'Sencha', 1);

SELECT o.order_id,
     c.display_name,
     ps.slot_label,
     o.drink_name,
     o.quantity
FROM drink_orders AS o
JOIN cafe_customers AS c
ON c.customer_id = o.customer_id
LEFT JOIN pickup_slots AS ps
ON ps.slot_id = o.slot_id
ORDER BY o.order_id;

-- break it: uncomment — quantity must satisfy the CHECK constraint
-- INSERT INTO drink_orders (order_id, customer_id, drink_name, quantity)
-- VALUES (103, 2, 'Batch Brew', 0);

⌘/Ctrl + Enter to run

Constraints are the database saying, "I know what valid means here." Next, 9.3 focuses on the hardest design choice inside those constraints: what kind of key should identify a row — natural, surrogate, identity, UUID, or composite?

Checkpoint

Answer all three to mark this lesson complete