Relational Model — Keys, FK Integrity, NULL Traps & Modeling Judgment (Deep Dive)

The core Relational Model pages already cover what a key is, what a foreign key is, and how to draw the line between a 1:1, 1:M, and M:N relationship. This page picks up exactly where those stop: the traps that only bite once real NULLs, real concurrency, and real key-design decisions enter the picture — the layer a senior interviewer actually probes.

1. The NULL semantics trap: NOT IN can silently return zero rows

SQL is three-valued: every comparison evaluates to TRUE, FALSE, or UNKNOWN, and NULL compared to anything — including another NULL — is always UNKNOWN. A WHERE clause keeps a row only when the predicate is TRUE; UNKNOWN is treated exactly like FALSE for that purpose, but it does not behave like FALSE inside a boolean expression, and that gap is where the bug lives.

x NOT IN (a, b, c) is not a primitive — the engine expands it to x<>a AND x<>b AND x<>c. If any one of a, b, c is NULL, that one comparison is UNKNOWN. TRUE AND UNKNOWN is UNKNOWN — not TRUE — so the whole AND chain can never resolve to TRUE once a single NULL is in the list, no matter how many of the other comparisons are genuinely TRUE. Every row gets rejected.

Traced example

Table emp(emp_id, manager_id): employees 2 and 3 report to employee 1 (the CEO), and the CEO's own manager_id is NULL (nobody manages the CEO).

emp_id | manager_id
-------+-----------
1      | NULL      -- CEO, no manager
2      | 1
3      | 1

SELECT emp_id FROM emp
WHERE emp_id NOT IN (SELECT manager_id FROM emp);  -- "find employees who manage no one"

The subquery returns (NULL, 1, 1). Trace row 2 (a genuine non-manager): 2 NOT IN (NULL, 1, 1) expands to 2<>NULL AND 2<>1 AND 2<>1 = UNKNOWN AND TRUE AND TRUE = UNKNOWN → row 2 is dropped. Trace row 3: identical shape → UNKNOWN → dropped. Trace row 1 (the CEO, who genuinely does manage people, so should be excluded anyway): also UNKNOWN, dropped for the wrong reason. The query returns zero rows, even though employees 2 and 3 are correct, honest answers — one stray NULL in the subquery poisoned the entire result set.

The fix

-- Fix 1: NOT EXISTS is NULL-safe because it never compares against the NULL directly
SELECT e.emp_id FROM emp e
WHERE NOT EXISTS (SELECT 1 FROM emp m WHERE m.manager_id = e.emp_id);

-- Fix 2: filter the NULL out of the list before NOT IN sees it
SELECT emp_id FROM emp
WHERE emp_id NOT IN (SELECT manager_id FROM emp WHERE manager_id IS NOT NULL);

Both correctly return {2, 3}. The rule to internalize: never write NOT IN against a subquery column that can be NULL — prefer NOT EXISTS as the default-safe habit.

A related NULL trap: UNIQUE is not PRIMARY KEY-strength

A UNIQUE constraint enforces "no two non-NULL values are equal" — but since NULL <> NULL is UNKNOWN rather than FALSE, most engines (Postgres, MySQL, Oracle, SQLite) let a UNIQUE column hold many NULL rows simultaneously (SQL Server is the outlier, allowing exactly one). If you are using a column as an alternate key — meant to identify a row the same way the primary key does — a bare UNIQUE does not give you that guarantee; you need UNIQUE NOT NULL to match primary-key identity semantics. Only PRIMARY KEY bundles uniqueness and non-nullability by definition.

2. Natural vs surrogate keys — the decision, not just the definitions

Three concrete PK strategies exist, and each has an operational cost the term-definition version of this topic skips:

• Auto-increment (SERIAL / IDENTITY): dense, monotonically increasing. Cheap to index and compare, but every concurrent insert targets the same rightmost B-tree leaf page — a hot-page write bottleneck under high write concurrency (buffer-lock contention on that one page). It also leaks business volume: an invoice numbered 48213 tells a competitor roughly how many invoices you've issued, which is a real reason some systems obscure or offset the sequence externally.
• UUIDv4 (fully random): solves the leak and, being generable client-side, the coordination problem — but the randomness scatters every insert across the entire key space. In a clustered-index engine (InnoDB) or any B-tree secondary index that stores the PK as a pointer, this means near-every insert touches a cold, unpredictable page, causing frequent page splits and fragmenting the index (lower fill factor, more disk I/O, worse cache hit rate).
• UUIDv7 / ULID (time-ordered): a 48-bit millisecond timestamp prefix followed by random bits. Globally unique and generable client-side like v4, but the monotonic prefix restores sequential-insert locality — new keys mostly land on the same right-edge page, the way auto-increment does. This is the modern default recommendation precisely because it keeps the auto-increment locality benefit without the central-sequence coordination or the volume leak (it does still leak rough creation time, a smaller trade-off).

Two rules that don't change no matter which PK style you pick:

• A surrogate key never removes the need for a natural UNIQUE constraint. If users has a surrogate user_id but no UNIQUE on email, nothing stops the same person being inserted twice with two different user_ids — you now have duplicate business rows that the surrogate key cannot see, because uniqueness of the surrogate says nothing about uniqueness of the real-world entity it represents.
• A wide composite natural key propagates into every child table. If Enrollment's natural key is (student_id, course_id, semester), then any table that references an enrollment — Submission, Grade_Appeal — must carry all three columns as its FK, bloating every downstream row and every index that includes it. A single surrogate enrollment_id collapses that FK back to one narrow column everywhere it's referenced.

3. FK referential integrity is a concurrency mechanism, not just a check

When a child row is inserted or updated, the engine must confirm the referenced parent row still exists at commit time — and to stop that parent from being deleted or changed mid-check, it takes a share-mode lock on the specific parent row being referenced (e.g. Postgres takes a FOR KEY SHARE row lock during the FK check). Share locks are mutually compatible, so many concurrent child inserts against different parent rows never contend.

The trap is a hot parent row: a popular category, a default tenant, a single "system" account that huge numbers of child rows reference. Every concurrent insert into the child table takes a share lock on that same parent row. Share locks don't block each other, but the moment any transaction needs an exclusive lock on that parent (an UPDATE or DELETE, including one queued by ON UPDATE CASCADE), it must wait for every outstanding share lock to release — and every child insert arriving after it queues behind that exclusive-lock request too. A single hot row becomes a serialization point invisible in the schema diagram and only visible under load.

Referential actions and deferred checking

• ON UPDATE CASCADE / RESTRICT / SET NULL govern what happens to children when the parent key changes. CASCADE is the most lock-expensive on a hot parent — it must acquire a lock on every affected child row in addition to the parent, widening the exact contention window above.
• DEFERRABLE INITIALLY DEFERRED postpones the FK check from per-statement to end-of-transaction. Two situations force it: (1) circular FKs — table A references B and B references A, so neither row can be inserted first under an immediate check; deferring lets both inserts land in one transaction and validates only at COMMIT. (2) bulk loads — loading child rows before their parents exist yet (e.g. restoring a dump table-by-table) is only safe if the check waits until every table is loaded.
• Composite FK with partial NULLs — MATCH SIMPLE vs MATCH FULL: for a composite FK like (dept_id, team_id), the SQL standard default, MATCH SIMPLE, says if any one column is NULL, the whole constraint is skipped — a row with (dept_id=NULL, team_id=5) is accepted even if no parent row has team_id=5, because the NULL short-circuits the check entirely. MATCH FULL is stricter: either all composite columns are NULL (constraint skipped) or all must match one parent row — a mix of NULL and non-NULL is rejected outright. Choosing MATCH SIMPLE by default (as most engines do) can silently admit half-populated FK rows; use MATCH FULL when a partial reference should never be legal.

4. Junction tables are forced by the model, not a design taste

Derive it from what a column physically is: a foreign-key column stores exactly one value per row (first normal form — atomic values only). One column, one value, means a child row can point at at most one parent — which is precisely the definition of a 1:M relationship (many children, each with one parent) or a 1:1 (the same, plus a uniqueness constraint on the FK column). There is no way to widen a single column to hold several parent ids without breaking atomicity.

M:N is therefore not representable by a single FK on either side — neither entity's row can hold "many" in one column. The only way to record a set of pairings is a third table whose rows — not columns — hold the multiplicity: one row per (A_id, B_id) pairing, each row's FK to A and FK to B individually satisfying the one-value-per-column rule. That third table (the junction/associative table) is a structural consequence of what a column is, not an optional convention.

Correcting a real modeling defect: composite PK does not equal "1:1"

A common and genuinely invalid pattern shows up when converting a 1:1 relationship (Employee ↔ Payroll, one payroll record per employee): marking both Payroll_ID and Employee_ID as the composite primary key of Payroll, i.e. PRIMARY KEY (Payroll_ID, Employee_ID). This looks plausible but is wrong: a composite PK only guarantees the pair is unique. It says nothing about Employee_ID alone — so two different Payroll_IDs can each carry the same Employee_ID, and the composite-PK check happily accepts both rows. The schema you meant to be 1:1 has silently become 1:M.

-- WRONG: composite PK only forbids duplicate (Payroll_ID, Employee_ID) pairs,
-- not duplicate Employee_ID — two payroll rows per employee slip through
CREATE TABLE Payroll (
  Payroll_ID  INT,
  Employee_ID INT,
  Monthly_Salary DECIMAL,
  PRIMARY KEY (Payroll_ID, Employee_ID),
  FOREIGN KEY (Employee_ID) REFERENCES Employee(Employee_ID)
);

-- CORRECT, option A: FK-as-PK — Employee_ID alone is the primary key
CREATE TABLE Payroll (
  Employee_ID INT PRIMARY KEY,
  Monthly_Salary DECIMAL,
  FOREIGN KEY (Employee_ID) REFERENCES Employee(Employee_ID)
);

-- CORRECT, option B: keep a surrogate PK, enforce 1:1 with a separate UNIQUE
CREATE TABLE Payroll (
  Payroll_ID  INT PRIMARY KEY,
  Employee_ID INT UNIQUE NOT NULL,
  Monthly_Salary DECIMAL,
  FOREIGN KEY (Employee_ID) REFERENCES Employee(Employee_ID)
);

Option A is the cleanest 1:1 pattern: the FK column is the PK, so the engine rejects a duplicate Employee_ID at the identity layer itself, before any application logic runs. Option B keeps a separate surrogate identity for Payroll rows but adds UNIQUE NOT NULL to do the cardinality job the PK alone cannot: two independent constraints, each responsible for one guarantee.

5. The 1:1 merge-vs-split decision

A 1:1 relationship does not automatically mean two tables. Default to one merged table unless one of these forces a split:

• Optional / sparse attributes. If only a small fraction of parent rows have the related data (e.g. 5% of employees have expat-relocation records), merging fills most rows with NULL columns; splitting keeps the common table narrow.
• Rarely-accessed attributes hurt the hot path. If the 1:1 side carries a dozen large or infrequently-read columns, every read of the frequently-accessed side (e.g. Employee.name) drags irrelevant bytes through the buffer cache. Splitting keeps the hot table small and cache-friendly.
• Security / compliance partitioning. Payroll and salary data often need row/column-level access control, encryption, or audit logging distinct from general HR data. Splitting lets you grant SELECT on Employee without exposing Payroll at all — a merge would force the same access policy on both.

If none of these apply, splitting is premature normalization that only adds a join. When you do split, use the FK-as-PK pattern from §4 — never a composite key that lets the constraint drift into 1:M.

6. Generated columns and what each normal form actually removes

GENERATED ALWAYS AS (expression) STORED is the modern SQL answer to a derived attribute (e.g. full_name from first_name || ' ' || last_name, or total_price from qty * unit_price) — instead of the older choice of "store it and hope a trigger keeps it in sync" or "never store it, recompute on every read," the engine itself recomputes the value on every write and guarantees it is never stale, because it is not application code that can be forgotten. STORED persists the computed value on disk (readable, indexable, costs write time + space); some engines (MySQL) also offer VIRTUAL, computed on read with no storage cost. This removes the derived-attribute redundancy risk without giving up queryability — the same "drop it and recompute" instinct that removes attributes like Age or Net_Pay from a schema, but now the recomputed value can still live in the row.

Each higher normal form removes one specific redundancy-caused anomaly, each with its own concrete trigger:

• 2NF removes the partial-dependency anomaly. Trigger: a table keyed on (student_id, course_id) that also stores course_name — course_name depends only on course_id, not the whole key. Update anomaly: renaming a course means updating it in every enrollment row that references it, and missing one leaves two names for the same course.
• 3NF removes the transitive-dependency anomaly. Trigger: (employee_id PK, dept_id, dept_manager) — dept_manager depends on dept_id, which depends on employee_id, not on employee_id directly. Update anomaly: the department's manager changes, and every employee row in that department must be found and updated.
• BCNF removes anomalies 2NF/3NF miss when candidate keys overlap. Classic trigger: (Student, Subject, Instructor) where each instructor teaches exactly one subject (Instructor → Subject) but a subject may be taught by several instructors. Candidate keys are {Student,Subject} and {Student,Instructor}; since Subject is part of a candidate key it's a prime attribute, so 3NF does not flag the dependency at all. But Instructor (the determinant) is not itself a superkey, so BCNF does. The anomaly: if the last student enrolled under (Student, Physics, Dr. Smith) drops the course and that row is deleted, the independent fact "Dr. Smith teaches Physics" is lost entirely — a deletion anomaly 2NF/3NF cannot see.

7. Query-plan awareness (pointer, not a re-derivation)

Every judgment on this page — which key to index, whether a FK column needs its own index, whether a composite key's column order matters — ultimately resolves at the optimizer, which decides between a sequential scan, an index scan, and a join algorithm based on selectivity and statistics. That mechanism is taught in depth on "How a Query Executes — Parse, Plan, Run & EXPLAIN" in the Query Execution topic; this page intentionally does not re-derive it, only flags that a key/FK decision is never complete without checking what the plan actually does with it.

Pitfalls

• NOT IN against a nullable subquery column. Silently returns zero rows the moment any value in the list is NULL — the single most common relational-model interview trap. Default to NOT EXISTS.
• Trusting UNIQUE to behave like a primary key for NULLs. It admits many NULLs in most engines; add NOT NULL explicitly if the column must be exactly-one-per-row.
• Composite PK mistaken for a cardinality guarantee. PRIMARY KEY (A, B) only forbids duplicate pairs — it says nothing about A alone or B alone. Using it to "enforce" a 1:1 (§4) silently permits 1:M.
• Ignoring hot-parent-row lock contention. A popular default/category/tenant row referenced by thousands of concurrent child inserts becomes an invisible serialization point the moment any transaction needs an exclusive lock on it.
• MATCH SIMPLE admitting half-populated composite FKs. The SQL-standard default skips the entire FK check if any one composite column is NULL — a partially-NULL row can slip through silently.
• Random UUID PKs in a clustered-index engine. Postgres avoids the InnoDB-style heap-clustering cost (its heap is unordered/append-only, so insert location doesn't depend on PK value), but the PK's own B-tree index still scatters and page-splits under random UUIDv4 inserts just like any B-tree — prefer UUIDv7/ULID on Postgres too if you care about index bloat/cache locality, not only on InnoDB/MySQL.

Judgment layer

• Natural vs surrogate key: pick a surrogate (auto-increment or UUIDv7/ULID) as the PK for any OLTP table referenced by multiple FKs, to keep every join and index narrow — but always keep the natural business key as a separate UNIQUE NOT NULL constraint, never drop it just because a surrogate exists.
• 1:1 merge vs split: merge by default; split only for sparsity, hot-path cache locality, or security/compliance partitioning — and split with FK-as-PK, never a composite key.
• When to reach for DEFERRABLE INITIALLY DEFERRED: circular FK references and multi-table bulk loads where per-statement checking cannot succeed in any valid insert order — not a general-purpose "turn off checking" switch.
• M:N is never optional: the moment either side of a relationship needs "many" in what would be a single FK column, a junction table is mechanically forced, not a stylistic choice.

Related pages

• Keys in Relational Databases — Databases — the key fundamentals this deep dive builds on
• Relational Integrity Constraints — Databases — base FK/constraint concepts extended here
• MVCC & Locking — Snapshots, Row Locks & Deadlocks — Databases — the locking mechanics behind FK contention
• Normalization — 3NF vs BCNF Dependency-Preservation, Lossless Join, Synthesis & 5NF (Deep Dive) — Databases — deeper treatment of the normal forms cited here
• How a Query Executes — Parse, Plan, Run & EXPLAIN — Databases — the optimizer mechanics every key/FK choice ultimately hits

---

Re-authored/Deepened for this guide.

I just read a lesson on **Relational Model — Keys, FK Integrity, NULL Traps & Modeling Judgment (Deep Dive)** (Databases) and want to truly understand it. Explain Relational Model — Keys, FK Integrity, NULL Traps & Modeling Judgment (Deep Dive) from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Relational Model — Keys, FK Integrity, NULL Traps & Modeling Judgment (Deep Dive)** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Relational Model — Keys, FK Integrity, NULL Traps & Modeling Judgment (Deep Dive)** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Relational Model — Keys, FK Integrity, NULL Traps & Modeling Judgment (Deep Dive)** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.