ON DELETE and ON UPDATE

CASCADE, SET NULL and RESTRICT decide what happens to child rows when a parent row is deleted or its key changes.

SyntaxFOREIGN KEY (col, ...) REFERENCES parent (col, ...) [ON DELETE opt] [ON UPDATE opt]

A foreign key says a child row must point at a parent row. A referential action says what MySQL does to the child when that parent is deleted or its key is updated. Both are declared on the child table.

[CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name, ...)
  REFERENCES tbl_name (col_name, ...)
  [ON DELETE reference_option]
  [ON UPDATE reference_option]

reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

The five options

Option	Effect
`RESTRICT`	Reject the delete or update on the parent.
`NO ACTION`	In InnoDB, equivalent to `RESTRICT`; the operation is rejected immediately.
`CASCADE`	Apply the same delete or key update to the matching child rows.
`SET NULL`	Set the child's foreign key columns to `NULL`. The columns must be nullable.
`SET DEFAULT`	Recognised by the parser, but InnoDB rejects any table definition that uses it.

Omitting a clause is the same as writing NO ACTION, so the default behaviour under InnoDB is to refuse.

Choosing

Ask whether the child can mean anything without its parent. An order line is part of an order: CASCADE. An order is not part of a customer, it is a record about one: RESTRICT. A post's author is an attribution that may go missing: SET NULL.

ON UPDATE only matters if the parent key can change. With an AUTO_INCREMENT surrogate key it never does, so ON UPDATE RESTRICT is the honest declaration.

Example

CREATE TABLE orders (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  placed_at   DATETIME NOT NULL,
  PRIMARY KEY (id),

  -- An order is a record ABOUT a customer, not a part of one. Deleting a
  -- customer who has orders must fail and force a decision (anonymise,
  -- archive, refuse). RESTRICT is the same as omitting both clauses under
  -- InnoDB, but writing it says the choice was made on purpose.
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers (id)
    ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE order_items (
  id        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id  BIGINT UNSIGNED NOT NULL,
  sku       VARCHAR(32) NOT NULL,
  qty       INT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  KEY idx_order_items_order (order_id),   -- the FK needs this index

  -- A line item is PART of the order. It has no meaning alone, so it goes
  -- when the order goes.
  CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id) REFERENCES orders (id)
    ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB;

When to use it

Delete an order and have its line items go with it, in one statement.
Refuse to delete a customer who still has orders, instead of silently orphaning them.
Keep published posts readable after their author's account is removed.
Audit an inherited schema for accidental CASCADE chains before running a bulk delete.

More examples

SET NULL for an optional relationship

The right action when the content must outlive the relationship — published articles, audit entries, closed tickets. The cost is that author_id becomes nullable everywhere, so the JOIN becomes a LEFT JOIN and every display path needs a fallback. Take SET NULL only when you are willing to write that fallback.

CREATE TABLE posts (
  id        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  -- Nullable is not optional here: SET NULL on a NOT NULL column produces
  -- a table definition MySQL will not accept.
  author_id BIGINT UNSIGNED NULL,
  title     VARCHAR(200) NOT NULL,
  body      MEDIUMTEXT NOT NULL,
  PRIMARY KEY (id),
  KEY idx_posts_author (author_id),

  CONSTRAINT fk_posts_author
    FOREIGN KEY (author_id) REFERENCES users (id)
    ON DELETE SET NULL ON UPDATE RESTRICT
) ENGINE=InnoDB;

-- Now every read has to handle the orphan case explicitly.
SELECT p.title, COALESCE(u.display_name, 'Deleted user') AS author
FROM posts p
LEFT JOIN users u ON u.id = p.author_id;

Auditing what the actions actually are

Run this when you inherit a schema, and again before any bulk delete against a parent table. A cascade chain three tables deep will remove rows nobody in the room expected, and REFERENTIAL_CONSTRAINTS is the only place that shows the whole map at once.

-- The definition in your migration file and the definition in the database
-- diverge over time. This is the authoritative answer.
SELECT rc.TABLE_NAME            AS child_table,
       rc.CONSTRAINT_NAME       AS fk_name,
       rc.REFERENCED_TABLE_NAME AS parent_table,
       rc.DELETE_RULE,
       rc.UPDATE_RULE
FROM information_schema.REFERENTIAL_CONSTRAINTS rc
WHERE rc.CONSTRAINT_SCHEMA = DATABASE()
ORDER BY rc.REFERENCED_TABLE_NAME, rc.TABLE_NAME;

-- Before deleting a parent, see how far a CASCADE chain reaches: any child
-- listed with DELETE_RULE = 'CASCADE' that is itself a parent of another
-- CASCADE row will keep the deletion travelling.

Changing an action on an existing foreign key

The migration you write when a RESTRICT turns out to be wrong, or when a CASCADE is deleting more than intended. Be aware that adding a foreign key uses the in-place algorithm only when foreign_key_checks is disabled; with checks on, MySQL falls back to the copying algorithm, so on a large child table this is a table rebuild, not a metadata change.

-- MySQL has no statement that modifies a foreign key in place. Drop it by
-- symbol and add it back with the action you want.
ALTER TABLE order_items DROP FOREIGN KEY fk_order_items_order;

ALTER TABLE order_items
  ADD CONSTRAINT fk_order_items_order
  FOREIGN KEY (order_id) REFERENCES orders (id)
  ON DELETE CASCADE ON UPDATE RESTRICT;

-- Note: DROP FOREIGN KEY takes the constraint symbol, DROP INDEX takes the
-- index name. They are separate objects and dropping the key leaves the
-- index behind, which is usually what you want.

Discussion

Be the first to comment on this lesson.

The five options

Choosing

Example

When to use it

More examples

SET NULL for an optional relationship

Auditing what the actions actually are

Changing an action on an existing foreign key

Related

Discussion