Does a foreign key have to point at the parent table's primary key? Explain what a foreign key actually requires of the attributes it references.
answer
- FK target = candidate key, not necessarily primary
- non-unique target = reference denotes many rows = meaningless
- composite key referenced in full, never partially
- match by value, not by pointer or row address
- mutable target = updates ripple into every child
basics
~20 sNo. A foreign key must reference a candidate key of the parent, meaning a declared unique, minimal attribute set. The primary key is the usual target because it is stable and non-null, but any enforced candidate key works. Referencing non-unique attributes is meaningless because the reference would not denote one row.
solid answer
~60 sA foreign key expresses referential integrity: every non-null value of the referencing attribute set must appear as the value of the referenced attribute set in some parent row. For that statement to denote a single parent row, the referenced attribute set must be **unique**, that is a candidate key of the parent. It does not have to be the *primary* key; an enforced alternate key is a legal target. Three consequences follow. First, a composite key must be referenced in full: you cannot reference half of a two-attribute key, because half of it is not unique. Second, the reference is by value, not by physical location, and the referencing attributes must be comparable in type with their targets. Third, nullability is the child's business, not the parent's: a nullable foreign key means "no parent", while composite foreign keys raise the question of what a partially-null reference should mean. In practice teams still point almost everything at the primary key, because it is the identifier chosen for stability and it is guaranteed non-null. Pointing at a mutable alternate key like email means every change to that value ripples through the children.
go deeper
State the core rule: a foreign key value must exist in the parent, and the referenced attributes must be unique there.
Explain why uniqueness of the target is required, and that a composite key must be referenced in full.
Discuss target selection tradeoffs, mutable targets causing update ripples, and partially-null composite references as a design smell to eliminate.
Treat identity targets as a contract across services and integrations, and set a house rule on immutable reference targets rather than deciding table by table.
## What a foreign key asserts A foreign key is a constraint between two relations, child and parent. It says: for the attribute set FK in the child and the attribute set K in the parent, every tuple of the child whose FK values are all non-null must have some parent tuple with matching K values. That is referential integrity. Nothing about navigation, joins or indexes belongs in the definition; those are consequences the engine may add. ## Why the target must be a candidate key The purpose of a reference is to designate *one* thing. If the referenced attribute set were not unique, then a child value could match three parent rows and the phrase "the parent of this row" would have no meaning. The model therefore requires the referenced attribute set to be a key of the parent: unique and, in the strict formulation, minimal. So the honest answer to "must a foreign key point at the primary key" is no, it must point at a candidate key, and the primary key is merely the candidate key most often chosen. If the parent has an alternate key that is genuinely enforced, referencing it is legal and sometimes useful, for example when an external system already knows the parent by a business code and joining through it avoids an extra lookup. The emphasis belongs on *enforced*. If the target's uniqueness is only believed and not declared, the reference silently becomes ambiguous the day a duplicate appears. This is why the constraint is normally stated against a declared key rather than any column you happen to believe is unique. ## Composite and partial references When the parent's key is composite, the child must carry the entire key. Referencing a proper subset is not allowed, because a proper subset of a minimal key is not unique. A child of a relation keyed by `{student_id, course_id, term}` must therefore repeat all three attributes, which is the propagation cost that makes wide natural keys expensive in deep hierarchies. Composite foreign keys also raise the question of partial nulls: what should it mean when one attribute of a two-attribute reference is set and the other is null? The standard offers different matching interpretations, ranging from "any null means the constraint is satisfied" to "either all attributes are null or all are non-null". The safe design position is to avoid the situation entirely by making composite foreign keys wholly nullable or wholly mandatory, because a half-populated reference is almost never a fact anyone intended to record. ## Values, not pointers A foreign key matches by value. There is no address, row identifier or physical link involved, which is exactly what makes the relational model independent of storage layout. Two practical consequences follow: the referencing and referenced attributes must be type-comparable, and changing a referenced value is a real data change that must be propagated to children rather than an invisible pointer update. That propagation cost is the strongest argument for pointing references at immutable identifiers. ## Choosing the target in practice Default to the primary key. It was elected precisely because it is stable, always known and narrow, and entity integrity guarantees it is non-null, so a matching child value always denotes a real row. Consider an alternate key as a target only when it is immutable, enforced, and already the identifier used by the systems doing the referencing, and accept that you are now committed to never changing that value. Self-references are ordinary foreign keys where child and parent are the same relation, as in a manager attribute referencing an employee. They behave exactly the same way: the target must still be a candidate key, and a null means "no parent" rather than "unknown parent", a distinction worth stating explicitly in a design review. ## What is not part of the definition What happens when a referenced row is deleted or updated is a policy attached to the constraint, and how the engine checks the constraint efficiently is an implementation matter. The taxonomy question is narrower and cleaner: a foreign key is a value-based assertion that some candidate key of the parent contains this value.
- Why is pointing a foreign key at an email or business code riskier than pointing it at a generated identifier?Because foreign keys match by value, so if the referenced value changes, every child row holding the old value is now dangling and must be updated in the same logical change. Business codes and contact details are exactly the values that get corrected over time. A stable, meaningless identifier removes that ripple entirely, at the cost of one extra join when you only know the business value.
- A child relation references a parent whose key is {a, b}. Can the child reference only a?No. A proper subset of a minimal key is not unique, so a value of a alone can match several parent rows and the reference would not designate one row. The child must carry both attributes and reference them together as one constraint. Two separate single-attribute constraints are not equivalent, since they would allow a combination that exists nowhere in the parent.
saying these in an interview costs you the question
- Insisting a foreign key can only reference a primary key
- Thinking a foreign key stores a pointer or physical row address rather than a value
- Referencing a column that is merely believed unique but has no enforced uniqueness rule
- Splitting a composite reference into independent single-attribute constraints
- Confusing the nullability of the child's foreign key with a property of the parent's key