Constraints in PostgreSQL

Constraints enforce data rules inside the database—so every client (psql, Python, Django admin) gets the same guarantees.

Constraint types

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  dept TEXT NOT NULL,
  salary NUMERIC(12,2) CHECK (salary > 0),
  UNIQUE (dept, email)
);

Practice: Apply DDL in a throwaway practice database. Use \d table in psql to verify constraints and indexes.

Deferrable foreign keys

ALTER TABLE order_items
  ADD CONSTRAINT fk_order
  FOREIGN KEY (order_id) REFERENCES orders(id)
  DEFERRABLE INITIALLY DEFERRED;

Deferred checks run at COMMIT—useful for bulk loads with temporary ordering issues.

Exclusion constraints (preview)

Exclusion constraints prevent overlapping ranges (scheduling). Requires btree_gist extension for some types—advanced topic beyond intro DDL.

Important interview questions and answers

1. Q: UNIQUE NULL behavior?
   A: Multiple NULLs allowed in UNIQUE columns in Postgres—NULL is distinct.
2. Q: CHECK vs app validation?
   A: Both—CHECK catches bad data from ad-hoc SQL and legacy scripts.

Self-check

1. Which constraint ensures emails are distinct?
2. Can you have two NULLs in a UNIQUE column?

Tip: CHECK constraints protect data from ad-hoc SQL and legacy scripts—not only your app.