Constraints
Foreign key actions
In the database design chapters, you saw that a foreign key prevents a row from referencing a record that doesn't exist.
For example:
1order_items.order_id → orders.idensures that every order_items.order_id refers to a valid order.
But foreign keys raise another question:
What should happen to the related rows if the referenced row is deleted?
PostgreSQL lets us define this behavior using foreign key actions.
What happens by default?
Consider an order that has related order items.
The relationship looks like this:
1234567891011ordersid = 2 │ │ ▼order_itemsorder_id = 2order_id = 2order_id = 2If we try to delete the order while its order items still reference it, PostgreSQL normally prevents the deletion.
For example, order 2 has a related order item in our database.
Try:
12DELETE FROM ordersWHERE id = 2;PostgreSQL rejects the delete because removing the order would leave an order_items row referring to an order that no longer exists.
The foreign key protects referential integrity.
ON DELETE
When defining a foreign key, we can specify what PostgreSQL should do when the referenced row is deleted.
For example:
123order_id INTEGERREFERENCES orders(id)ON DELETE CASCADEThe action after ON DELETE determines what happens to rows that reference the deleted row.
PostgreSQL supports several actions:
12345NO ACTIONRESTRICTCASCADESET NULLSET DEFAULTNO ACTION
NO ACTION is the default.
For example:
123order_id INTEGERREFERENCES orders(id)ON DELETE NO ACTIONIf an order still has referencing order items when the foreign key is checked, PostgreSQL rejects the delete.
Conceptually:
1234567DELETE order 2 │ ▼order_items still reference order 2 │ ▼delete rejectedIf no action is specified, PostgreSQL behaves as though NO ACTION had been specified.
RESTRICT
RESTRICT also prevents deletion while referencing rows exist:
123order_id INTEGERREFERENCES orders(id)ON DELETE RESTRICTFor ordinary application use, both NO ACTION and RESTRICT usually mean:
1You cannot delete the parent while child rows still reference it.There is a more advanced difference involving when PostgreSQL checks the constraint: NO ACTION can participate in deferred constraint checking when the constraint is configured as deferrable, while RESTRICT does not permit the referenced row to be removed in that way.
For most application schemas, the important point is that both protect the referenced row from being deleted while dependent references remain.
CASCADE
Sometimes the related row should disappear when its parent is deleted.
For example, an order_item exists only because its order exists.
In such a design, we might use:
123order_id INTEGERREFERENCES orders(id)ON DELETE CASCADENow deleting the order also deletes its related order items.
A simplified view looks like this:
The deletion automatically cascades from the referenced row to the rows that depend on it.
Trying CASCADE safely
Rather than changing the foreign keys in our existing tables, create two small demonstration tables:
12345678910CREATE TABLE demo_orders ( id INTEGER PRIMARY KEY);
CREATE TABLE demo_order_items ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES demo_orders(id) ON DELETE CASCADE);Insert one order and two order items:
1234567INSERT INTO demo_orders (id)VALUES (1);
INSERT INTO demo_order_items (id, order_id)VALUES (1, 1), (2, 1);Verify the order items:
123SELECT *FROM demo_order_itemsORDER BY id;You should see:
1234 id | order_id----+---------- 1 | 1 2 | 1Now delete the order:
12DELETE FROM demo_ordersWHERE id = 1;Run:
12SELECT *FROM demo_order_items;No rows remain.
PostgreSQL automatically removed them because the foreign key was defined with:
1ON DELETE CASCADESET NULL
Another option is:
1ON DELETE SET NULLSuppose a row refers to another row, but it can continue to exist even after that relationship disappears.
PostgreSQL can replace the foreign-key value with NULL.
Conceptually:
12345678910111213before deletionproduct_id = 10 │ ▼Product 10delete Product 10 │ ▼product_id = NULLFor this to work, the foreign-key column must allow NULL.
For example, this would conflict with the action:
123product_id INTEGER NOT NULLREFERENCES products(id)ON DELETE SET NULLbecause PostgreSQL would be instructed both to set the value to NULL and to prevent NULL.
SET DEFAULT
PostgreSQL also supports:
1ON DELETE SET DEFAULTWhen the referenced row is deleted, PostgreSQL replaces the foreign-key value with the column's default value.
That default must still satisfy the foreign key.
For example, setting:
1product_id = 1as the default only works if a referenced product with id = 1 exists.
SET DEFAULT is less common than CASCADE, NO ACTION, or SET NULL, but it is available when the data model has a meaningful fallback row.
Choosing the right action
The correct foreign key action depends on what the relationship means.
Consider:
1orders → order_itemsAn order item normally has no meaning without its order.
If deleting an order is allowed, deleting its order items as well may make sense:
1ON DELETE CASCADENow consider:
1products ← order_itemsHistorical order items may need to remain even if a product is no longer sold.
Automatically deleting historical order items because someone deletes a product could destroy important order history.
In that case, preventing deletion may make more sense.
The question to ask is:
Should the referencing row continue to exist if the referenced row disappears?
If the answer is no, CASCADE may be appropriate.
If the answer is yes, you may need RESTRICT, NO ACTION, SET NULL, or a different data model.
CASCADE should be deliberate
CASCADE is convenient, but it can also delete a large amount of related data.
Imagine:
1234567customer │ ▼orders │ ▼order_itemsIf cascading deletes were configured through the entire chain, deleting one customer could potentially delete:
12345customer+all of their orders+all order items belonging to those ordersThat may be exactly what the application wants.
Or it may be a serious data-loss bug.
Always choose cascading behavior based on ownership and business requirements rather than adding CASCADE automatically.
ON UPDATE
Foreign keys can also specify what happens when the referenced key value changes.
For example:
12REFERENCES customers(id)ON UPDATE CASCADEIf the referenced customer ID changes, PostgreSQL updates matching foreign-key values automatically.
PostgreSQL supports actions for ON UPDATE similar to those available for ON DELETE.
In many application schemas, primary-key values are designed not to change, so ON UPDATE actions are encountered less frequently than ON DELETE actions.
Foreign key actions and indexes are different
A foreign key action determines what PostgreSQL should do when referenced data changes.
It does not determine how efficiently PostgreSQL finds the referencing rows.
As you saw earlier, PostgreSQL does not automatically create an index on the referencing foreign-key column.
For example:
1order_items.order_idmay benefit from an index when PostgreSQL frequently needs to find the order items belonging to a particular order.
Constraint behavior and indexing solve different problems:
12345678foreign key→ protects relationshipsON DELETE / ON UPDATE→ defines what happens when referenced rows changeindex→ can make finding rows more efficientBefore continuing, remove the demonstration tables:
12DROP TABLE demo_order_items;DROP TABLE demo_orders;The important point is:
Foreign keys do more than prevent invalid references. Their actions also let you define what should happen to related data when referenced rows are deleted or updated.