Relational Integrity Constraints

The four guarantees that keep a relational database honest

Integrity constraints are rules the engine enforces on every write, so bad data is rejected at the source rather than discovered later. Four layers:

• Domain constraints — every value comes from its column's domain: the type (INT, DATE), plus CHECK (price >= 0) and NOT NULL.
• Key constraints — declared candidate keys are UNIQUE; no two rows collide on them.
• Entity integrity — primary-key attributes are NOT NULL and unique. The reasoning is strict: a NULL primary key means "this row can't be identified", which defeats the point of a key — so it's forbidden.
• Referential integrity — a foreign key value must either be NULL or match an existing primary-key value in the referenced table. No order may point at a customer who doesn't exist (no "orphans").

Referential integrity, traced

CREATE TABLE Customers (id INT PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE Orders (
  id          INT PRIMARY KEY,
  customer_id INT REFERENCES Customers(id)   -- the foreign key
);
INSERT INTO Customers VALUES (1,'Ada'), (2,'Linus');

INSERT INTO Orders VALUES (10, 1);   -- OK: customer 1 exists
INSERT INTO Orders VALUES (11, 99);  -- REJECTED: no customer 99
--   ERROR: insert or update on table "orders" violates foreign key constraint

The second insert is refused at write time — the database will not let the orphan exist. The mirror-image question is what happens to children when you delete the parent, and that's a design decision you declare with ON DELETE:

ON DELETE …	DELETE Customers WHERE id = 1 does…	use when
RESTRICT / NO ACTION (default)	rejects the delete while order 10 still references it	orders must never be silently lost
CASCADE	deletes customer 1 and order 10	children are meaningless without the parent (e.g. cart items)
SET NULL	keeps order 10, sets customer_id = NULL	child outlives parent; column must be nullable

Pitfall that bites in production

A foreign key forces the engine to check the child table on every parent delete/update. If the child's FK column is not indexed, that check is a full scan of the child — a parent delete on a large table can lock and crawl. MySQL/InnoDB auto-creates an index for every FK; PostgreSQL does not. On Postgres you must add the index on the referencing column yourself, or cascading deletes get pathologically slow.

Takeaways

• Entity integrity = PK not-null + unique (an unidentifiable row is forbidden); referential integrity = no orphan FKs.
• ON DELETE RESTRICT|CASCADE|SET NULL is a deliberate choice about what a child means without its parent.
• Index your FK columns on PostgreSQL — it won't do it for you, and unindexed FKs make parent deletes scan the child.

---

Deepened for this guide (the prior version only named the constraint types). FK indexing behaviour per the PostgreSQL & MySQL/InnoDB docs. See also: Keys, Transactions & ACID, Indexing & Storage.

🎯 STANDOUT elevation: Why / example / when-not / failure / panel / drills — Relational Integrity Constraints

Why this exists / the decision it encodes

Constraints exist because application-only validation races and is incomplete: two concurrent inserts can both "check then write" an orphan. The engine enforces domain/key/entity/referential integrity on every write path so the database remains a single source of truth even when clients lie, crash, or race.

Worked example with numbers or traced SQL/FD

Customers: (1,Ada) (2,Linus)
Orders: insert (10,1) OK; insert (11,99) REJECT — no customer 99
DELETE Customers WHERE id=1 with order 10 present:
  RESTRICT → error (order 10 still references 1)
  CASCADE  → deletes order 10 too
  SET NULL → order 10.customer_id becomes NULL (column must allow NULL)
Index trap (Postgres): no index on orders.customer_id → parent delete scans whole Orders
to validate RESTRICT/CASCADE children. InnoDB auto-indexes FK; PG does not.

When NOT / named alternative

Do not CASCADE delete when children are financial history (orders, ledger lines) — use RESTRICT or soft-delete. Do not SET NULL if the business requires every order to have a customer forever. Do not replace FKs with "we validate in the API" for multi-writer systems; use FKs unless you have a deliberate eventual-consistency story across services.

Failure mode / ops fingerprint / interview trap

Ops: CASCADE wiping production child rows because someone dropped a parent "cleanup" without checking dependents. Interview trap: claiming FKs "hurt scale so we never use them" without measuring — the real cost is the missing child index on Postgres, not the constraint itself.

Domain judgment (K11 theory-bridge / K12 concurrency / K13 query-judgment)

K11: integrity constraints are the runtime enforcement of the relational model's key axioms. K12: concurrent writers without FKs can create orphans between check and commit. K13: FK indexes are schema-for-access-path decisions, not pure theory.

Hostile-panel drills (with model answers)

Q1. Entity integrity vs referential integrity — one sentence each.
Model answer: Entity: primary key attributes are NOT NULL and unique so every row is identifiable. Referential: every non-NULL FK value must match an existing referenced key (no orphans).

Q2. Why index FK columns on PostgreSQL?
Model answer: Deletes/updates of the parent must locate matching children; without an index that is a full scan of the child table under the parent lock — cascading deletes become pathological.

Q3. Defend RESTRICT as default for Orders→Customers.
Model answer: Orders outlive accidental customer deletion; RESTRICT forces an explicit business decision (reassign or archive) rather than silent CASCADE data loss.

I just read a lesson on **Relational Integrity Constraints** (Databases) and want to truly understand it. Explain Relational Integrity Constraints 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 Integrity Constraints** 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 Integrity Constraints** 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 Integrity Constraints** 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.