A foreign key does more than mark a relationship between tables: it dictates what the database does to child rows when you delete the parent. That reaction is called a referential action, and you pick it with the ON DELETE clause. Choose wrong and you either get an error on every delete or silently lose data.
A base schema and a link
Take three familiar tables. orders has a foreign key to users, and employees references itself through manager_id.
CREATE TABLE users (
id bigint PRIMARY KEY,
email text UNIQUE NOT NULL,
name text,
country text,
created_at timestamptz DEFAULT now()
);
CREATE TABLE orders (
id bigint PRIMARY KEY,
user_id bigint NOT NULL,
amount numeric(12,2),
status text,
created_at timestamptz DEFAULT now(),
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
Now DELETE FROM users WHERE id = 7 automatically removes every order of that user. Without an ON DELETE clause the default action is NO ACTION: deleting a parent that still has children just fails with an error.
The four delete actions
CASCADE deletes the child rows along with the parent. Great for strictly dependent data (line items inside an order), dangerous for anything that has value on its own.SET NULL clears the reference in the child row. The FK column must be nullable. Useful when the child outlives the parent: drop the user but keep the orders with user_id = NULL.RESTRICT blocks deleting the parent while any child exists. The check fires immediately.NO ACTION is the same default behaviour, but the check can be deferred to the end of the transaction in a deferrable constraint.
ALTER TABLE orders
DROP CONSTRAINT fk_orders_user,
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL;
There is also ON UPDATE: the same actions fire when the parent's primary key itself changes. In practice you almost never mutate a PK, so ON UPDATE CASCADE is rarely needed.
When cascade is dangerous
ON DELETE CASCADE is seductive because "everything cleans itself up". But it also erases data with no confirmation and no trace in the application.
DELETE FROM users WHERE country = 'RU';
Gotcha: cascades chain. If orders has a child order_items also set to CASCADE, deleting one user wipes three levels deep. At scale that is one long, blocking transaction and a bloated WAL. For financial or audit data prefer RESTRICT or a soft delete (deleted_at), and leave real DELETE to cleanup jobs.
Indexing the foreign key column
PostgreSQL automatically indexes the primary key a FK points to, but it does NOT create an index on the referencing column itself. Without one, every delete or update of a parent triggers a sequential scan of the child table to find dependent rows.
CREATE INDEX idx_orders_user_id ON orders (user_id);
This matters most with CASCADE and SET NULL, because the child check runs on every parent delete. The same rule applies to the self-referencing table:
CREATE INDEX idx_employees_manager ON employees (manager_id);
Deferrable constraints and self-references
Sometimes you need to break integrity temporarily inside a transaction, for example to swap rows around or load data with circular references. For that you declare the constraint DEFERRABLE:
CREATE TABLE employees (
id bigint PRIMARY KEY,
name text,
manager_id bigint,
dept text,
salary numeric(12,2),
CONSTRAINT fk_emp_manager
FOREIGN KEY (manager_id) REFERENCES employees(id)
ON DELETE SET NULL
DEFERRABLE INITIALLY DEFERRED
);
With INITIALLY DEFERRED the check is postponed until COMMIT, so you can hold dangling references inside the transaction. Remember the engine differences:
- MySQL (InnoDB) supports
CASCADE, SET NULL, RESTRICT and NO ACTION, but has no DEFERRABLE: every check is immediate.
- ClickHouse has no foreign keys at all: integrity is the application's job and relationships are modelled with
JOIN.
The baseline rule: cascade for strictly dependent data, SET NULL for children that outlive the parent, RESTRICT for anything valuable, and always index the FK column.
A foreign key does more than mark a relationship between tables: it dictates what the database does to child rows when you delete the parent. That reaction is called a referential action, and you pick it with the
ON DELETEclause. Choose wrong and you either get an error on every delete or silently lose data.A base schema and a link
Take three familiar tables.
ordershas a foreign key tousers, andemployeesreferences itself throughmanager_id.CREATE TABLE users ( id bigint PRIMARY KEY, email text UNIQUE NOT NULL, name text, country text, created_at timestamptz DEFAULT now() ); CREATE TABLE orders ( id bigint PRIMARY KEY, user_id bigint NOT NULL, amount numeric(12,2), status text, created_at timestamptz DEFAULT now(), CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE );Now
DELETE FROM users WHERE id = 7automatically removes every order of that user. Without anON DELETEclause the default action isNO ACTION: deleting a parent that still has children just fails with an error.The four delete actions
CASCADEdeletes the child rows along with the parent. Great for strictly dependent data (line items inside an order), dangerous for anything that has value on its own.SET NULLclears the reference in the child row. The FK column must be nullable. Useful when the child outlives the parent: drop the user but keep the orders withuser_id = NULL.RESTRICTblocks deleting the parent while any child exists. The check fires immediately.NO ACTIONis the same default behaviour, but the check can be deferred to the end of the transaction in a deferrable constraint.-- orders keep living, the link is cleared ALTER TABLE orders DROP CONSTRAINT fk_orders_user, ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL;There is also
ON UPDATE: the same actions fire when the parent's primary key itself changes. In practice you almost never mutate a PK, soON UPDATE CASCADEis rarely needed.When cascade is dangerous
ON DELETE CASCADEis seductive because "everything cleans itself up". But it also erases data with no confirmation and no trace in the application.-- one innocent-looking statement DELETE FROM users WHERE country = 'RU'; -- and every order of every matching user is gone tooGotcha: cascades chain. If
ordershas a childorder_itemsalso set toCASCADE, deleting one user wipes three levels deep. At scale that is one long, blocking transaction and a bloated WAL. For financial or audit data preferRESTRICTor a soft delete (deleted_at), and leave realDELETEto cleanup jobs.Indexing the foreign key column
PostgreSQL automatically indexes the primary key a FK points to, but it does NOT create an index on the referencing column itself. Without one, every delete or update of a parent triggers a sequential scan of the child table to find dependent rows.
CREATE INDEX idx_orders_user_id ON orders (user_id);This matters most with
CASCADEandSET NULL, because the child check runs on every parent delete. The same rule applies to the self-referencing table:CREATE INDEX idx_employees_manager ON employees (manager_id);Deferrable constraints and self-references
Sometimes you need to break integrity temporarily inside a transaction, for example to swap rows around or load data with circular references. For that you declare the constraint
DEFERRABLE:CREATE TABLE employees ( id bigint PRIMARY KEY, name text, manager_id bigint, dept text, salary numeric(12,2), CONSTRAINT fk_emp_manager FOREIGN KEY (manager_id) REFERENCES employees(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED );With
INITIALLY DEFERREDthe check is postponed untilCOMMIT, so you can hold dangling references inside the transaction. Remember the engine differences:CASCADE,SET NULL,RESTRICTandNO ACTION, but has noDEFERRABLE: every check is immediate.JOIN.The baseline rule: cascade for strictly dependent data,
SET NULLfor children that outlive the parent,RESTRICTfor anything valuable, and always index the FK column.