Primary and Foreign Key Constraints

1What a Constraint Actually Is

A constraint is a rule the database engine enforces on every single write, rejecting any INSERT/UPDATE that would violate it — it is not application-level validation that can be bypassed by a different client, a script, or a bug. 02-schema_design_and_data_modeling/ (section 4) covered the choice between a surrogate and a natural primary key; this topic covers how primary keys, foreign keys, and the other column-level constraints are actually declared, enforced, and inspected once that choice is made.

2Primary Key: Identity, Enforced Structurally

A primary key is really two constraints bundled together — NOT NULL and UNIQUE — plus a designation that this is the column (or columns) a table's rows are identified by. A table may have at most one primary key.

CREATE TABLE product (
  id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku   TEXT NOT NULL,
  price NUMERIC(10, 2) NOT NULL
);

Declaring PRIMARY KEY makes the engine build a unique index on that column automatically — every uniqueness check on insert is really an index lookup, not a table scan (the index mechanics themselves are covered in databases/indexing-storage/). This is why a table is never without an efficient way to answer "does this id already exist?" or "give me the row for this id" — the primary key's index guarantees both, unconditionally, for free.

3Composite Primary Keys

A primary key can span more than one column — the combination must be unique, even though no single column is:

CREATE TABLE enrolment (
  student_id  INT  REFERENCES student(id),
  course_code TEXT REFERENCES course(code),
  grade       TEXT,
  PRIMARY KEY (student_id, course_code)   -- the PAIR is the identity
);

The same student_id can appear in many rows (one per course), and the same course_code can appear in many rows (one per enrolled student) — what the constraint forbids is the exact same (student_id, course_code) pair appearing twice. This is the standard shape for a junction table (03-relationships/, section 4): the composite primary key both enforces "no duplicate pairing" and, as a side effect, is exactly the two foreign keys the junction table needed anyway — no extra column required unless the junction row must be referenced independently by a third table.

4Foreign Key: Referential Integrity, Enforced on Every Write

A foreign key says "this column's value must exist as a primary key (or unique-constrained column) value in another table" — and, unlike a comment or a naming convention, the engine actually checks it on every insert and update:

CREATE TABLE order_item (
  order_id   INT NOT NULL REFERENCES "order"(id),
  product_id INT NOT NULL REFERENCES product(id),
  quantity   INT NOT NULL CHECK (quantity > 0)
);

INSERT INTO order_item (order_id, product_id, quantity) VALUES (1, 9999, 2);
-- ERROR: insert or update on table "order_item" violates foreign key constraint
-- DETAIL: Key (product_id)=(9999) is not present in table "product".

A foreign key can only reference a column (or column set) that itself has a PRIMARY KEY or UNIQUE constraint on the parent table — the engine refuses to create a foreign key pointing at an unconstrained column, because without a uniqueness guarantee on the parent side "the row this points to" would be ambiguous. A composite foreign key references a composite primary/unique key the same way, but as a single all-or-nothing unit:

CREATE TABLE enrolment_note (
  student_id  INT,
  course_code TEXT,
  note        TEXT,
  FOREIGN KEY (student_id, course_code) REFERENCES enrolment(student_id, course_code)
);

What happens to a foreign key's value when the row it points to is deleted or its key changes (RESTRICT / CASCADE / SET NULL / SET DEFAULT) is covered in full in 03-relationships/ (section 7) — that's a referential-action question, layered on top of the referential-integrity constraint this topic is about.

5UNIQUE: Uniqueness Without Being "the" Identity

UNIQUE enforces no-duplicates exactly like a primary key's implicit uniqueness, but a table can have many UNIQUE constraints, and — critically — a UNIQUE column is nullable, and standard SQL treats NULL as never equal to another NULL, so multiple NULLs are allowed even in a UNIQUE column:

CREATE TABLE employee (
  id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email        TEXT UNIQUE NOT NULL,     -- natural-key lookup, per 02, section 4
  badge_number TEXT UNIQUE               -- nullable: not every employee has one YET, but no two can share one
);

INSERT INTO employee (email, badge_number) VALUES ('a@x.com', NULL);
INSERT INTO employee (email, badge_number) VALUES ('b@x.com', NULL); -- succeeds — NULLs don't collide
INSERT INTO employee (email, badge_number) VALUES ('c@x.com', 'B-1');
INSERT INTO employee (email, badge_number) VALUES ('d@x.com', 'B-1'); -- ERROR: duplicate key value

This is exactly the behavior that makes UNIQUE the right tool for "at most one non-empty value across all rows," and the wrong tool if the intent was "every row must have a distinct, present value" — that second intent needs UNIQUE paired explicitly with NOT NULL, as email is above.

6NOT NULL, CHECK, and DEFAULT

Three more constraint types that don't involve uniqueness at all:

CREATE TABLE product (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       TEXT NOT NULL,
  price      NUMERIC(10, 2) NOT NULL CHECK (price > 0),
  currency   TEXT NOT NULL DEFAULT 'USD',
  status     TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'discontinued'))
);

• NOT NULL — rejects a missing value outright; the cheapest and most common constraint, and the first thing to reach for whenever "this field is optional" isn't actually true for the domain (see 03-relationships/, section 6, for the same reasoning applied specifically to foreign key columns).
• CHECK (expression) — rejects any row where the boolean expression evaluates to false; runs on every insert and update, and can reference multiple columns of the same row (CHECK (discount_price < price)), but never another table — cross-table rules need a trigger or application logic instead.
• DEFAULT — supplies a value when the INSERT doesn't specify one; it is not a constraint in the rejection sense (it never causes a write to fail), but it's declared and enforced at the same layer, and combines naturally with NOT NULL (a default guarantees a value exists, NOT NULL guarantees it can never be cleared later).

Under the HoodHow it works internally▾

Every constraint check is an index lookup, not a table scan

A PRIMARY KEY, a UNIQUE constraint, and a FOREIGN KEY's reference side are all backed by a real index the engine builds automatically. Checking "does this id already exist" on insert, or "does the referenced row exist" for a foreign key, is a single indexed lookup against that index — O(log n), not a scan of the whole table. This is precisely why a foreign key is only allowed to reference a PRIMARY KEY/UNIQUE column: without that backing index, every insert into the child table would require an unindexed scan of the entire parent table to verify the reference, which no engine does silently.

Constraint checking has a timing, and it's usually immediate

By default, every constraint (including foreign keys) is checked immediately — at the exact statement that would violate it, mid-transaction. This occasionally collides with a legitimate need to insert mutually-referencing rows in one transaction (a references b and b references a). PostgreSQL allows a foreign key to be declared DEFERRABLE INITIALLY DEFERRED, which pushes its check to transaction COMMIT time instead of per-statement:

sqlCopy

ALTER TABLE employee
  ADD CONSTRAINT fk_favorite_project
  FOREIGN KEY (favorite_project_id) REFERENCES project(id)
  DEFERRABLE INITIALLY DEFERRED;

This is a narrow, deliberate escape hatch — not a way to "turn off" integrity checking, since the constraint is still fully enforced, just at a different point in the same transaction.

Constraints are catalog objects, not just DDL syntax sugar

Every constraint created gets a name (auto-generated if not given one, like product_price_check or employee_email_key) and a row in PostgreSQL's pg_constraint catalog table, queryable like any other data:

sqlCopy

SELECT conname, contype FROM pg_constraint WHERE conrelid = 'product'::regclass;
-- contype: 'p' primary key, 'f' foreign key, 'u' unique, 'c' check

This is what makes constraints introspectable and droppable/alterable independently (ALTER TABLE product DROP CONSTRAINT product_price_check;) instead of being baked irreversibly into the table's original definition.

⚠️ Common Pitfalls

1. Referencing a column with no UNIQUE/PRIMARY KEY constraint — the engine rejects the foreign key at DDL time; "it looks unique in practice" is not the same as an enforced uniqueness constraint, and the FK creation will fail outright.
2. Assuming UNIQUE forbids multiple NULLs the same way it forbids duplicate values — it doesn't; if "must always be present and unique" is the real intent, pair UNIQUE with NOT NULL explicitly.
3. Writing a CHECK constraint that references another table — not supported; a CHECK only sees the row being written, so cross-table invariants need a trigger, not a CHECK.
4. Forgetting DEFAULT doesn't retroactively apply — adding DEFAULT 'USD' to a column doesn't change existing rows that are already NULL; a default only fires on future inserts that omit the column.
5. Confusing a composite primary key's column order with an index-usability guarantee elsewhere — PRIMARY KEY (student_id, course_code) optimizes lookups by student_id (or both), but not efficiently by course_code alone; a leading-column mismatch here has the same index-usability consequences covered in databases/indexing-storage/.

📋 Quick Reference

PRIMARY KEY   = NOT NULL + UNIQUE + "this is the row's identity"; exactly one per table
                composite PK -> the COMBINATION is unique, not each column alone

FOREIGN KEY   = must reference a PK/UNIQUE column on the parent table
                enforced via an indexed lookup on every insert/update of the child
                what happens on parent delete/update -> 03-relationships/, section 7

UNIQUE        = no duplicate non-NULL values; multiple NULLs ARE allowed
                pair with NOT NULL when every row must have a distinct, present value

NOT NULL      = rejects missing values outright
CHECK (expr)  = rejects rows failing a boolean expression; single-row only, no cross-table rules
DEFAULT       = supplies a value on INSERT when omitted; not retroactive to existing rows

All constraints are catalog objects (pg_constraint) -> introspectable, independently droppable
Foreign keys default to IMMEDIATE checking; DEFERRABLE INITIALLY DEFERRED delays to COMMIT

🎤 Interview Q&A

🛠️ Practice exercises

Each exercise is a file in this topic folder. Code exercises are self-checking: run them and make the checks pass.

✅ Check yourself

Source: dbmodel_primary_foreign_keys_constraints_theory.md · generated by tools/build_lessons.py