Foreign keys and ON DELETE

Part of the free LogicWiz SQL & Databases course, Module 7: Creating tables. The queries and practice run in the app on real Postgres.

Meher deleted the Electronics department. Forty students still say dept_id = 2, pointing at a department that no longer exists. One line in CREATE TABLE would have stopped her, or cleaned up for her.

You'll learn to

• Link tables with REFERENCES (a foreign key)
• Predict what CASCADE, SET NULL and RESTRICT do on delete
• Choose the right ON DELETE for a real relationship
• Create and drop linked tables in the right order

Learn

A foreign key points at a parent

A foreign key is a column whose value must already exist in another table's primary key (or UNIQUE column).

• departments is the parent (referenced table).
• students.dept_id is the child column (referencing).

Postgres then guarantees referential integrity: no student can point at a department that does not exist. It checks when you insert or update a child, and when you delete or change a parent.

REFERENCES

CREATE TABLE departments (
  dept_id  INT PRIMARY KEY,
  name     VARCHAR(40) NOT NULL
);
CREATE TABLE students (
  roll_no  INT PRIMARY KEY,
  name     VARCHAR(40) NOT NULL,
  dept_id  INT REFERENCES departments(dept_id)
);

INSERT INTO departments VALUES (1, 'Computer Science'), (2, 'Electronics');
INSERT INTO students VALUES (101, 'Aarav', 1), (102, 'Meher', 2), (103, 'Riya', 1);

SELECT s.name, d.name AS department
FROM students AS s
JOIN departments AS d ON d.dept_id = s.dept_id;

Create the parent first: students cannot reference a table that does not exist yet. Insert parents first for the same reason.

A child with no parent is refused

CREATE TABLE departments (dept_id INT PRIMARY KEY, name VARCHAR(40) NOT NULL);
CREATE TABLE students (
  roll_no  INT PRIMARY KEY,
  dept_id  INT REFERENCES departments(dept_id)
);
INSERT INTO departments VALUES (1, 'Computer Science');
INSERT INTO students VALUES (101, 9);

insert or update on table "students" violates foreign key constraint: there is no department 9.

Deleting a parent that has children

CREATE TABLE departments (dept_id INT PRIMARY KEY, name VARCHAR(40) NOT NULL);
CREATE TABLE students (
  roll_no  INT PRIMARY KEY,
  dept_id  INT REFERENCES departments(dept_id)
);
INSERT INTO departments VALUES (1, 'Computer Science'), (2, 'Electronics');
INSERT INTO students VALUES (101, 1), (102, 2);
DELETE FROM departments WHERE dept_id = 2;

By default Postgres refuses: student 102 still points at department 2. This is Meher's safety net. You choose a different behaviour with ON DELETE.

ON DELETE: what happens to the children

ON UPDATE takes the same options for when a parent's key changes, but keys rarely change, so it is used much less.

CASCADE and SET NULL in action

CREATE TABLE partners (partner_id INT PRIMARY KEY, name VARCHAR(40) NOT NULL);
CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  partner_id  INT REFERENCES partners(partner_id) ON DELETE SET NULL
);
CREATE TABLE order_items (
  order_id  INT REFERENCES orders(order_id) ON DELETE CASCADE,
  item      VARCHAR(40) NOT NULL
);
INSERT INTO partners VALUES (1, 'Ramesh'), (2, 'Imran');
INSERT INTO orders VALUES (1001, 1), (1002, 2);
INSERT INTO order_items VALUES (1001, 'Masala Dosa'), (1001, 'Filter Coffee'), (1002, 'Biryani');

DELETE FROM partners WHERE partner_id = 1;   -- order 1001 stays, partner_id becomes NULL
DELETE FROM orders WHERE order_id = 1002;    -- its item goes with it

SELECT o.order_id, o.partner_id, i.item
FROM orders AS o
LEFT JOIN order_items AS i ON i.order_id = o.order_id;

Two deletes, two behaviours. Ramesh left, but order 1001 and its items are kept. Order 1002 was deleted, and CASCADE removed its Biryani row.

A foreign key column can be NULL, and NULL skips the check: a student with dept_id NULL has no department and that is allowed. If every child must have a parent, write dept_id INT NOT NULL REFERENCES departments(dept_id). And be careful with CASCADE: deleting one customer can silently delete years of orders.

Exams ask you to name referential integrity and to say what each ON DELETE option does. Interviews ask "what happens to a customer's orders when the customer is deleted?" The good answer: it depends on the FK, and orders usually use RESTRICT (keep the history) while order items use CASCADE.

In one line

REFERENCES parent(col) keeps children pointing at real parents; ON DELETE decides: refuse (default/RESTRICT), CASCADE, or SET NULL.

Interview corner

What is a foreign key and what is referential integrity?

A foreign key is a column (or set of columns) whose values must exist in another table's primary key or unique column. Referential integrity is the guarantee that results: no child row points at a parent that does not exist. The database checks it on every insert, update and delete.

Explain ON DELETE CASCADE, SET NULL and RESTRICT.

When a parent row is deleted: CASCADE deletes the child rows too, SET NULL keeps them and sets the FK column to NULL, and RESTRICT (or the default NO ACTION) refuses the delete while children exist. Example: order_items CASCADE from orders; deliveries SET NULL when a partner leaves; orders RESTRICT a restaurant from being deleted.

Can a foreign key be NULL? Can it reference a column that is not the primary key?

Yes to both. A NULL FK skips the check (add NOT NULL if a parent is required). It can reference any column with a PRIMARY KEY or UNIQUE constraint, such as email.

What is a self-referencing foreign key?

A foreign key that points at the same table, like employees.manager_id REFERENCES employees(emp_id) or customers.referred_by REFERENCES customers(customer_id). The top of the tree (the CEO) has NULL.