Primary Keys, Foreign Keys & Relationships

Primary keys

-- surrogate key: the usual choice
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
id bigint  GENERATED ALWAYS AS IDENTITY PRIMARY KEY   -- for tables that will grow

-- natural key
CREATE TABLE countries (code char(2) PRIMARY KEY, name text);

-- composite key
CREATE TABLE enrollments (
  student_id int, course_id int,
  PRIMARY KEY (student_id, course_id)
);

Unlike InnoDB, PostgreSQL tables are heap tables — rows are not physically stored in primary key order. That makes the choice of key less performance-critical than in MySQL, but the usual advice still holds: keep it small, immutable and meaningless.

For UUID keys, prefer a time-ordered UUID (v7) over a random v4: random UUIDs scatter inserts across the whole index and hurt cache locality.

Foreign keys

CREATE TABLE enrollments (
  id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  student_id integer NOT NULL,
  course_id  integer NOT NULL,
  CONSTRAINT fk_student FOREIGN KEY (student_id)
    REFERENCES students(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_course FOREIGN KEY (course_id)
    REFERENCES courses(id) ON DELETE RESTRICT
);

-- shorthand for a single-column reference
student_id integer NOT NULL REFERENCES students(id) ON DELETE CASCADE

The referenced column must have a unique or primary key constraint.

Index your foreign keys. Postgres creates an index on the referenced primary key, but not on the referencing column. Without one, every DELETE on the parent has to scan the child table, and joins are slow:

CREATE INDEX idx_enroll_student ON enrollments(student_id);

Referential actions

Action	On delete of the parent
NO ACTION (default)	Error, checked at the end of the statement
RESTRICT	Error, checked immediately
CASCADE	Delete the children too
SET NULL	Set the child column to NULL
SET DEFAULT	Set the child column to its default

Postgres 15 added column lists: ON DELETE SET NULL (dept_code) sets only the named column of a composite key.

Use CASCADE for true ownership (order → order items). For shared references prefer RESTRICT, so a mistaken delete fails loudly instead of quietly removing thousands of rows.

Relationship patterns

One-to-One    users ── profiles           FK + UNIQUE on the child
One-to-Many   departments ──< students    FK on the "many" side
Many-to-Many  students >──< courses       junction table with two FKs

CREATE TABLE student_courses (
  student_id  integer REFERENCES students(id) ON DELETE CASCADE,
  course_id   integer REFERENCES courses(id)  ON DELETE CASCADE,
  enrolled_at date NOT NULL DEFAULT current_date,
  PRIMARY KEY (student_id, course_id)
);
CREATE INDEX ON student_courses (course_id);   -- for the reverse lookup

A composite primary key on the junction table indexes the first column for free; add an index on the second column for queries in the other direction.

Inspecting relationships

SELECT
  tc.table_name, kcu.column_name,
  ccu.table_name AS references_table
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu USING (constraint_name)
JOIN information_schema.constraint_column_usage ccu USING (constraint_name)
WHERE tc.constraint_type = 'FOREIGN KEY';

-- foreign key columns with no index — a common performance bug
SELECT conrelid::regclass AS table_name, conname
FROM pg_constraint c
WHERE contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND (i.indkey::int2[])[0] = c.conkey[1]
  );