SQL Academy · Lesson

FOREIGN KEY and Referential Actions

Tie tables together with FOREIGN KEY, and choose ON DELETE CASCADE / SET NULL / RESTRICT for the right behaviour.

FOREIGN KEY and Referential Actions is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What a Foreign Key Does

A FOREIGN KEY makes the database refuse rows that point to non-existent parents:

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  total NUMERIC(10,2) NOT NULL
);

-- Inserting an order with user_id=999 fails if no such user exists.

Why Use Foreign Keys?

FKs enforce referential integrity:

No orphan rows
Catches application bugs at insert time
Makes ER diagrams accurate
Powers automatic cascade behaviour

Referential Actions

What should happen when the parent row is deleted or updated? Five options:

NO ACTION — error (default, deferrable)
RESTRICT — error (not deferrable)
CASCADE — also delete/update the child
SET NULL — set child FK to NULL
SET DEFAULT — set child FK to its default

ON DELETE CASCADE

"When the user is deleted, delete their orders too":

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL
    REFERENCES users(id) ON DELETE CASCADE,
  total NUMERIC(10,2) NOT NULL
);

ON DELETE SET NULL

"When the manager is removed, employees keep existing but lose their manager":

CREATE TABLE employees (
  id BIGSERIAL PRIMARY KEY,
  full_name TEXT NOT NULL,
  manager_id BIGINT REFERENCES employees(id) ON DELETE SET NULL
);

Default: RESTRICT-Like

Without a referential action, attempting to delete a parent with children fails:

ERROR:  update or delete on table "users" violates foreign key constraint "orders_user_id_fkey" on table "orders"
DETAIL:  Key (id)=(42) is still referenced from table "orders".

Always Index the FK Column

PostgreSQL does NOT create an index on the child side automatically. Add one — without it, deleting a parent triggers a full scan of every child table:

CREATE INDEX orders_user_id_idx ON orders(user_id);

Composite Foreign Keys

Reference a composite primary key:

CREATE TABLE order_items (
  order_id BIGINT,
  line_no INT,
  PRIMARY KEY (order_id, line_no)
);
CREATE TABLE shipments (
  order_id BIGINT,
  line_no INT,
  FOREIGN KEY (order_id, line_no)
    REFERENCES order_items(order_id, line_no)
);

Deferring Foreign Key Checks

Sometimes you need to insert a chicken-and-egg pair. Mark the FK as DEFERRABLE INITIALLY DEFERRED:

ALTER TABLE orders
  ALTER CONSTRAINT orders_user_id_fkey DEFERRABLE INITIALLY DEFERRED;

BEGIN;
  INSERT INTO orders ... ;     -- FK temporarily not checked
  INSERT INTO users ... ;
COMMIT;                         -- checked here

Adding Foreign Keys Online

On big tables, use NOT VALID + VALIDATE to avoid a long lock:

ALTER TABLE orders
  ADD CONSTRAINT orders_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fkey;

Cascade Carefully

CASCADE deletes propagate. Deleting a user can wipe orders, payments, audit logs. Make sure the cascade matches your business intent.

Recap

Foreign keys are guard rails.

REFERENCES + action declares the link and behaviour
Index the FK column on the child
Use NOT VALID + VALIDATE for online additions

Quick Check

What is the correct referential action to AUTOMATICALLY remove an order when its parent user is deleted?

Frequently asked questions

Is the “FOREIGN KEY and Referential Actions” lesson free?

Yes — the full text of “FOREIGN KEY and Referential Actions” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “FOREIGN KEY and Referential Actions”?

Tie tables together with FOREIGN KEY, and choose ON DELETE CASCADE / SET NULL / RESTRICT for the right behaviour. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “FOREIGN KEY and Referential Actions” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy