PostgreSQL Constraints

Constraints are rules the database enforces automatically — they're what keep your data correct without relying on application code to catch every mistake.

NOT NULL

Rejects any insert/update that leaves the column empty.

name VARCHAR(100) NOT NULL

UNIQUE

Guarantees no two rows share the same value.

email VARCHAR(150) UNIQUE

A UNIQUE constraint spanning multiple columns means the combination must be unique, not each column individually:

UNIQUE (student_id, course_id)

PRIMARY KEY

Combines NOT NULL + UNIQUE, and marks the column as the table's main identifier. A table can have only one primary key (though it can span multiple columns).

id SERIAL PRIMARY KEY

FOREIGN KEY

Links a column to a row in another table, ensuring you can't reference something that doesn't exist.

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  total NUMERIC(10, 2)
);

ON DELETE Behavior

Controls what happens to child rows when the referenced parent row is deleted:

customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE
-- deleting a customer also deletes their orders

customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL
-- deleting a customer just clears customer_id on their orders

customer_id INTEGER REFERENCES customers(id) ON DELETE RESTRICT
-- (the default) blocks deleting a customer that still has orders

CHECK

Enforces an arbitrary boolean condition on a column.

salary NUMERIC(10, 2) CHECK (salary > 0)
age INTEGER CHECK (age BETWEEN 18 AND 65)

DEFAULT

Not strictly a constraint, but works alongside them to fill in a value automatically.

is_active BOOLEAN DEFAULT true

Adding a Constraint to an Existing Table

ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);

Common Errors & Troubleshooting

• ERROR: duplicate key value violates unique constraint — an insert/update tried to write a value that already exists in a UNIQUE/PRIMARY KEY column; the error message names the specific constraint, which tells you exactly which column(s) conflicted.
• ERROR: insert or update on table "orders" violates foreign key constraint — you tried to reference a customer_id that doesn't exist in customers; insert the parent row first, or double-check the ID you're referencing.
• ERROR: update or delete on table "customers" violates foreign key constraint on a DELETE — this means the default ON DELETE RESTRICT behavior is blocking you because dependent orders still exist; either delete/reassign those orders first, or add ON DELETE CASCADE/SET NULL if that behavior is actually desired.

Key Takeaways

• PRIMARY KEY is really NOT NULL + UNIQUE combined, plus "this is the table's identity."
• FOREIGN KEY's ON DELETE clause decides how deleting a parent row cascades to its children.
• CHECK constraints let the database itself reject invalid values, rather than trusting application code.