A column in one table references the key of another table under a foreign key constraint. What exactly does that constraint guarantee about the stored data, and what happens if the referencing column holds NULL?
answer
- non-NULL child value must exist in parent key
- parent side must be PK or UNIQUE
- NULL child = no relationship, check skipped
- FK ≠ NOT NULL — add both for mandatory
- MATCH SIMPLE: any NULL skips composite check
basics
~20 sIt guarantees every non-NULL value in the referencing (child) column exists in the referenced (parent) key, so there are no orphan rows. A NULL referencing value points at nothing, so the check is skipped and the row is accepted.
solid answer
~50 sA foreign key is a referential-integrity rule: for every child row, the value in the referencing column(s) must exist as a value of the referenced parent key. The referenced side must be a primary key or unique key, because the engine has to resolve a value to exactly one parent row. The guarantee runs both ways: you cannot insert a child pointing at a non-existent parent, and you cannot delete a parent (or change its key) while children still point at it. While the constraint is enabled and validated, orphans are impossible. NULL is the deliberate escape hatch. A NULL referencing column has no referent, so the check is skipped and the row is accepted — that is how an optional relationship is modelled. If the relationship is mandatory, add NOT NULL next to the foreign key; the foreign key alone never forces presence. For composite foreign keys the default MATCH SIMPLE rule accepts the row if *any* referencing column is NULL; MATCH FULL requires all-NULL or all-non-NULL.
go deeper
State the core rule: non-NULL child values must exist in the parent key, NULL is allowed and means no relationship, and orphans are prevented.
Add that the parent side must be PK/unique, that the check fires on both child writes and parent deletes/key updates, and that NOT NULL is a separate constraint.
Bring in MATCH SIMPLE versus MATCH FULL for composite keys, and the fact that NOT VALID/unvalidated constraints do not vouch for existing rows.
Frame it as where the invariant lives: which guarantees you want the engine to hold at every commit versus what you accept reconciling elsewhere, and what that costs across bulk loads and replicas.
## The rule A foreign key constraint states: every non-NULL value in the referencing columns of the child table must also exist in the referenced key columns of the parent table. The child table carries the pointer; the parent table owns the identity being pointed at. An *orphan* is a child row whose pointer resolves to nothing — the exact state the constraint makes unreachable. ## Why the parent side must be unique The engine must be able to answer "does a parent with this value exist?" deterministically, and referential actions must know which row to follow. That is why standard SQL requires the referenced columns to be a primary key or to carry a unique constraint. Referencing a non-unique column is rejected at DDL time. This also means the parent side always has an index available for the existence probe — the child side does not get one automatically, which is a separate and much-discussed problem. ## Both directions are enforced People often think of a foreign key as an insert-time check only. It is a two-sided invariant: - Writing a child row (INSERT, or UPDATE that changes the referencing columns) triggers a lookup: does a matching parent exist right now? - Removing or renaming the parent identity (DELETE of the parent row, or UPDATE of the referenced key value) triggers the opposite check: are there children still pointing here? If yes, the operation is refused unless a referential action is defined to repair the children. Both checks happen inside the writing transaction, so the invariant holds at every commit point rather than being eventually reconciled. ## NULL means "no relationship" SQL's three-valued logic treats NULL as unknown/absent. A NULL referencing column is not a dangling pointer — it is the absence of a pointer, so there is nothing to validate and the row is accepted. This is intentional and useful: `orders.coupon_id` referencing `coupons(id)` and left NULL models "no coupon", with no sentinel row required. The consequence candidates miss is that a foreign key constrains *shape*, not *presence*. If business rules say every order must belong to a customer, `customer_id` needs both `REFERENCES customers(id)` and `NOT NULL`. Without NOT NULL, application bugs that forget to set the column produce rows that silently belong to nobody — and no constraint fires. ## Composite foreign keys and MATCH semantics When the key spans several columns, the standard defines three match rules: - MATCH SIMPLE (the default nearly everywhere): if any referencing column is NULL, the whole check is skipped. So `(tenant_id = 7, doc_id = NULL)` is accepted even though tenant 7 exists — a common source of half-populated pointers. - MATCH FULL: all referencing columns must be NULL together, or all non-NULL and matching a parent. - MATCH PARTIAL: rarely implemented. If a composite reference must be all-or-nothing, either declare MATCH FULL where supported or make all the columns NOT NULL. ## What the guarantee does not cover The constraint only holds while it is enabled and validated. Constraints created in a NOT VALID / NOVALIDATE state enforce new writes but assume nothing about the existing rows, so pre-existing orphans survive until a validation pass runs. Rows loaded through bulk-load paths that disable constraint checking, or replicas where the constraint was never created, can likewise carry orphans. And nothing about a foreign key implies the parent row is *correct* — only that it exists.
- How would you force every child row to actually have a parent?Declare the referencing column(s) NOT NULL in addition to the foreign key. The foreign key only validates values that are present; NOT NULL is what makes presence mandatory. For a composite key, mark every column NOT NULL (or use MATCH FULL) so half-populated pointers cannot occur.
- Can a foreign key reference a column that is not unique?No. Standard SQL requires the referenced columns to be a primary key or covered by a unique constraint, because the existence probe and any referential action must resolve to at most one parent row. Engines reject such a definition at DDL time; you would have to add a unique constraint to the parent first.
Like a postal address on an envelope: the constraint checks that the address exists on the map, but leaving the address blank is legal — the letter simply isn't going anywhere.
saying these in an interview costs you the question
- Claiming a foreign key makes the column mandatory (it does not — NOT NULL does)
- Thinking NULL in the referencing column violates the constraint
- Believing the check happens only on child INSERT and not on parent DELETE
- Assuming a composite foreign key rejects a row where only one column is NULL under default MATCH SIMPLE
- Saying a foreign key can point at any column, unique or not