When is ON DELETE SET NULL the appropriate referential action for a foreign key, what must be true of the schema for it to work, and how does ON DELETE SET DEFAULT compare?
answer
- Optional relationship → SET NULL; owned part → CASCADE
- Requires nullable columns; impossible inside the child's PK
- Does not propagate further down the chain
- NULL now means "never set" OR "parent deleted"
- SET DEFAULT needs a protected sentinel parent row
basics
~20 sUse SET NULL when the reference is optional and the child should outlive its parent — an article losing its category. It requires the child's foreign key columns to be nullable, so it cannot be used on NOT NULL columns or on columns that form part of the child's primary key. SET DEFAULT instead sets the column to its default, which must itself match an existing parent row.
solid answer
~60 s`ON DELETE SET NULL` says the relationship is **optional**: the child is a real entity that survives losing its parent. An article whose category is deleted becomes uncategorised; an employee whose manager leaves becomes unassigned. It fits exactly when NULL is a meaningful state for that column. Prerequisites: the child's foreign key columns must be nullable, so it is incompatible with NOT NULL and with a foreign key that participates in the child's primary key — in those cases the engine rejects the definition or the delete fails. Note also that the action stops there: nulling a child's reference does not propagate to the child's own children, since the child row was not deleted. `ON DELETE SET DEFAULT` writes the column's declared default instead. It only works if that default is itself a valid parent key — an "Unassigned" sentinel row must exist and must never be deleted — otherwise the operation fails with a foreign key violation. That extra prerequisite is why it is rare in practice. The semantic cost of SET NULL: NULL now conflates "never assigned" with "parent was removed".
code
sql · 12 linesCREATE TABLE article (
id BIGINT PRIMARY KEY,
category_id BIGINT NULL
REFERENCES category(id) ON DELETE SET NULL
);
-- SET DEFAULT: category 0 must exist and must never be deleted
CREATE TABLE product (
id BIGINT PRIMARY KEY,
category_id BIGINT NOT NULL DEFAULT 0
REFERENCES category(id) ON DELETE SET DEFAULT
);go deeper
Say SET NULL keeps the child row and blanks its reference, and that the column must allow NULL.
Add the incompatibility with NOT NULL and primary key columns, the fact that it does not propagate, and the sentinel-row requirement for SET DEFAULT.
Discuss the semantic collapse of NULL, the loss of the historical parent reference, composite foreign key matching, and how to protect a SET DEFAULT sentinel.
Position it within a lifecycle policy — which relationships are optional by design, whether history must be retained, and whether NULL or an explicit status column should carry that meaning across services.
## The relationship SET NULL encodes Every foreign key relationship is either *existential* (the child is a part of the parent and is meaningless without it) or *associative* (the child is an entity in its own right that happens to point at a parent). `ON DELETE CASCADE` is the natural action for the first kind; `ON DELETE SET NULL` is the natural action for the second. Good fits: - `article.category_id` — deleting a category should not destroy the articles; they become uncategorised. - `employee.manager_id` — a manager leaving should not delete their reports. - `ticket.assignee_id` — an agent account is removed; the ticket must survive, unassigned. The test is simple: **is NULL a state this column can meaningfully be in?** If yes, SET NULL is coherent. If NULL would mean "broken data", the relationship is existential and the right action is CASCADE or RESTRICT. ## Prerequisites and hard incompatibilities - **The foreign key columns must be nullable.** SET NULL on a NOT NULL column is a contradiction; engines reject it at definition time or fail at delete time. This is the number-one reason the action "doesn't work". - **The columns must not be part of the child's primary key**, because primary key columns are implicitly NOT NULL. A child keyed `(order_id, line_no)` can never use SET NULL on `order_id`. - **Composite foreign keys.** Under the default matching rule, setting the referencing columns to NULL satisfies the constraint, so the child row becomes half-identified. Whether that is acceptable depends on whether a partially-null reference means anything in your model; usually it argues for CASCADE or RESTRICT instead. ## What SET NULL does not do It does not propagate. Because the child row is not deleted, that child's own children still reference an existing row and nothing further happens. So SET NULL terminates a cascade chain — sometimes exactly what you want, sometimes a surprise for people who expect the whole subtree to react. It also does not preserve history. The old parent id is simply gone from the row. If "which category did this article used to be in?" matters, SET NULL destroys that information; you would need an archive table, an audit trail, or a soft-deleted parent instead. ## The semantic price After SET NULL, a NULL in that column means either "never had a parent" or "the parent was deleted". Those are different business facts collapsed into one representation. Reports and queries that treat NULL as "not yet triaged" quietly start counting orphaned rows too. If the distinction matters, model it explicitly — a status column, or a sentinel parent — rather than leaning on NULL to carry two meanings. ## SET DEFAULT and its chicken-and-egg problem `ON DELETE SET DEFAULT` writes the column's declared `DEFAULT` value into the child instead of NULL. The appeal is that it avoids NULL and gives you a named bucket: an `Unassigned` category, an `Unknown` region. The catch is that the new value must satisfy the same foreign key. So: - The column must have a default declared, and it must be non-null. - A parent row with exactly that key must exist at the moment the action runs — otherwise the delete fails with a foreign key violation, which is a confusing error to debug. - That sentinel row must be protected from deletion forever, or a later delete of the sentinel breaks everything at once. In practice this means a RESTRICT foreign key or a guard in application code. - Someone deleting the sentinel by cascade is a real incident mode. Engine support is also patchier than for the other actions, so verify before designing around it. ## Choosing between them - Child is a part of the parent → **CASCADE**. - Child is independent and "no parent" is a legitimate state → **SET NULL**. - Child is independent and you need a named bucket rather than NULL, and you are willing to maintain and protect a sentinel row → **SET DEFAULT**. - Child is independent and the parent should simply not be deletable while referenced → **RESTRICT / NO ACTION**. A useful default in application schemas: SET NULL for optional decoration, RESTRICT for anything with business or legal weight, CASCADE only for owned parts with bounded cardinality.
- Why can't ON DELETE SET NULL be used when the foreign key column is part of the child's primary key?Primary key columns are implicitly NOT NULL, and SET NULL requires writing NULL into the referencing columns. The two rules contradict, so the engine rejects the definition or the delete fails. Structurally this is also a signal that the child is an existential part of the parent, where CASCADE or RESTRICT is the right action anyway.
- What breaks if the sentinel row targeted by ON DELETE SET DEFAULT is itself deleted?Any subsequent parent deletion that triggers the action fails with a foreign key violation, because the default value no longer matches an existing parent row, and existing children still pointing at the sentinel become the target of that constraint too. The sentinel must be protected — typically with a RESTRICT foreign key or an application-level guard — for the design to hold.
saying these in an interview costs you the question
- Declaring SET NULL on a NOT NULL foreign key column and expecting it to work
- Expecting SET NULL to propagate down to the child's own children
- Assuming SET DEFAULT works without a matching parent row for the default value
- Using SET NULL where the child genuinely cannot exist without its parent
- Ignoring that NULL then conflates "never assigned" with "parent deleted"