SQL Reference ยท Constraints and Data Types
SQL ON DELETE and ON UPDATE actions
Reference for ON DELETE and ON UPDATE foreign key actions: CASCADE, SET NULL, SET DEFAULT, RESTRICT and NO ACTION, with runnable examples and dialect notes.
ON DELETE and ON UPDATE are optional parts of a foreign key. They choose what the database does with the child rows when the parent row they point at is deleted (ON DELETE) or has its key value changed (ON UPDATE).
The default is to refuse the change while children exist. The other actions let the database keep the data consistent for you: delete the children as well, set the reference to NULL, or restore a default value.
CREATE TABLE order_items ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders (id) ON DELETE CASCADE, note TEXT );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
FOREIGN KEY (column_name) REFERENCES parent_table (parent_column) [ON DELETE referential_action] [ON UPDATE referential_action] referential_action: NO ACTION | RESTRICT | CASCADE | SET NULL | SET DEFAULT
| Action | What happens to the child rows |
|---|---|
NO ACTION |
The default. The statement fails if children would be left without a parent. The check happens at the end of the statement (or of the transaction, if the constraint is deferred). |
RESTRICT |
Like NO ACTION, but the check is immediate and cannot be deferred. |
CASCADE |
Delete the children (ON DELETE) or copy the parent's new key to them (ON UPDATE). |
SET NULL |
Set the child's foreign key column to NULL. The column must allow NULL. |
SET DEFAULT |
Set the column to its DEFAULT value, which must itself match an existing parent. |
Dialect differences
| Dialect | Notes |
|---|---|
| SQLite | All five actions, only when PRAGMA foreign_keys = ON. |
| PostgreSQL | All five. SET NULL (col) and SET DEFAULT (col) can name which columns to change for composite keys (version 15). |
| MySQL | InnoDB supports CASCADE, SET NULL, RESTRICT, NO ACTION. SET DEFAULT is parsed but rejected by InnoDB. |
| SQL Server | All five, but cycles and multiple cascade paths are forbidden. |
| Oracle | ON DELETE CASCADE and ON DELETE SET NULL only, and no ON UPDATE. |
-- SQL Server: avoid "multiple cascade paths" errors by using NO ACTION on one path ALTER TABLE order_items ADD CONSTRAINT fk_items_product FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE NO ACTION;
Usage notes
CASCADEon delete is dangerous. Deleting one customer can silently erase their orders, invoices and payments. Use it for true "part-of" relationships, like order lines belonging to an order.- Soft deletes avoid the question: set an
is_deletedflag ordeleted_attimestamp and keep every row. ON UPDATE CASCADEis rarely needed with surrogate keys that never change.- Cascades fire other cascades, and their triggers too. A deep chain can delete a lot of rows in one statement.
- Index the child column. Every parent delete must find its children, and without an index this scans the child table for each deleted parent row.
SET NULLfits optional relationships, such as an employee's manager who left.- Constraint names matter for changing the action: in most databases you drop the constraint and add it back with the new action.
Related
FOREIGN KEY,DEFERRABLE,DELETE,UPDATE,PRAGMA- Lessons: Foreign keys, Delete, Relationships