Cardinality and Ordinality
How Many, and Whether Required
LinkedIn Hook
"Your
orderstable allowed a NULLcustomer_id. A nightly job created 14,000 ghost orders nobody could trace."Most developers learn relationships as "one-to-many" and stop there. But "one-to-many" is only half the story. The other half is the question your ER diagram should be screaming at you: is the one side required, or optional? Can an order exist without a customer? Can a customer exist without any orders? Can a profile exist without a user? Can a user exist without a profile?
Cardinality answers "how many" — one or many. Ordinality answers "is it required" — zero or one. Crow's-foot notation glues them together into four end markers that fit on the end of a single line: a single bar means "exactly one," a circle means "zero," a crow's foot means "many." Every relationship in your schema has two sets of these markers, one for each end. Get them wrong and your foreign keys allow garbage your business rules forbid.
The fix is mechanical, not magical. Mandatory participation ->
NOT NULLon the foreign key. Optional participation -> nullable foreign key. "At most one" on the child side ->UNIQUEconstraint on the FK column. "Exactly one" combines both:NOT NULLplus theREFERENCESclause itself. Four constraints, four end markers, one diagram that actually matches the database.In Lesson 8.5, I break down cardinality and ordinality end to end: the four crow's-foot symbols, mandatory vs optional participation, the four canonical combinations, and the exact PostgreSQL constraints that enforce each one.
Read the full lesson -> [link]
#SQL #Database #PostgreSQL #DataModeling #ERDiagram #BackendDevelopment #InterviewPrep
What You'll Learn
- The difference between cardinality (how many) and ordinality (whether required)
- The four crow's-foot end markers and what each one means at a glance
- Mandatory vs optional participation, and why it lives at the per-end level — not the relationship level
- The four canonical combinations: 1..1, 0..1, 1..N, 0..N — and what they look like in real schemas
- How to enforce each combination in PostgreSQL with
NOT NULL,UNIQUE,REFERENCES, andCHECK - Why "at least one" on the parent side cannot be enforced by a single FK column — and the deferred-constraint trick that fixes it
- MySQL differences for the same constraints (storage engines, deferred checks, FK enforcement)
- Common mistakes that turn an "always required" relationship into a silent NULL trap
The Wedding Invitation Analogy — Plus-Ones and Empty Chairs
Imagine you are arranging a wedding seating chart. Every guest has a chair, and every chair has at most one guest. That is one cardinality rule: each guest sits in exactly one chair. But there is a second rule hiding in the same sentence: must every chair have a guest? Maybe not — empty chairs are fine, the venue just over-ordered tables. And must every guest have a chair? Absolutely — a guest with no chair has nowhere to sit, so the rule is "every guest is required to have one."
Now flip it around to the chair's perspective. From the chair's side, the question is: how many guests fit in one chair? One — that is the cardinality. Is the chair required to have a guest? No — empty chairs are allowed. So the chair end of the relationship is "zero or one guest," while the guest end is "exactly one chair."
Both ends of the same relationship answer two different questions, and the answers are usually different on each side. That is the entire point of cardinality and ordinality. Cardinality is the count: one, or many. Ordinality is the requirement: zero (optional) or one-or-more (mandatory). Every relationship line in an ER diagram has two end markers — one for each entity — and each marker carries both pieces of information at once.
+---------------------------------------------------------------+
| TWO QUESTIONS, ONE LINE |
+---------------------------------------------------------------+
| |
| GUEST ----||--------------------------------O<---- CHAIR |
| ^ ^ |
| | | |
| "exactly one" "zero or one" |
| (mandatory, (optional, |
| cardinality 1) cardinality 1) |
| |
| Read it left to right: |
| "Each GUEST sits in exactly one CHAIR." |
| Read it right to left: |
| "Each CHAIR holds zero or one GUEST." |
| |
+---------------------------------------------------------------+The trap most people fall into: they describe the relationship from one side only ("one guest, one chair") and forget that the other side has its own answer. The chair side is optional — empty chairs are legal. The guest side is mandatory — a guest with no chair is a bug. If you only model one direction, you end up with a schema that allows guests without chairs, or chairs that mysteriously belong to nobody. The two ends are independent. Always model both.
The Four Crow's-Foot End Markers
Crow's-foot notation is the most common way to draw cardinality and ordinality on an ER diagram, because every combination fits inside a tiny symbol at the end of a line. There are exactly four building-block markers, and they combine to form the meaningful end-of-line shapes you actually draw.
+---------------------------------------------------------------+
| THE FOUR BUILDING BLOCKS |
+---------------------------------------------------------------+
| |
| | single bar -> "one" (cardinality = 1) |
| O open circle -> "zero" (optional) |
| < crow's foot -> "many" (cardinality = N) |
| || double bar -> "and only one" (mandatory + 1) |
| |
+---------------------------------------------------------------+
+---------------------------------------------------------------+
| THE FOUR USEFUL END MARKERS |
+---------------------------------------------------------------+
| |
| ||---- "exactly one" mandatory, cardinality 1 |
| O|---- "zero or one" optional, cardinality 1 |
| ||-<-- "one or many" mandatory, cardinality N |
| O<--- "zero or many" optional, cardinality N |
| |
| Read each marker as TWO answers stacked on top of each |
| other: |
| - The OUTER symbol (closest to the entity) = ordinality |
| - The INNER symbol (closest to the line) = cardinality |
| |
+---------------------------------------------------------------+The mental rule that makes crow's foot click: read the symbol nearest the entity first, then the symbol nearest the line. The outer symbol tells you the minimum (zero or one — that is ordinality). The inner symbol tells you the maximum (one or many — that is cardinality). So O< is "minimum zero, maximum many" -> "zero or many." And || is "minimum one, maximum one" -> "exactly one."
+---------------------------------------------------------------+
| READING A FULL RELATIONSHIP LINE |
+---------------------------------------------------------------+
| |
| CUSTOMER ||----------------------------------O<-- ORDER |
| ^ ^ |
| | | |
| exactly one zero or many |
| customer per orders per |
| order customer |
| |
| Sentence form: |
| "Each ORDER belongs to exactly one CUSTOMER." |
| "Each CUSTOMER has zero or many ORDERS." |
| |
+---------------------------------------------------------------+Notice how each end describes the other side of the relationship. The marker next to CUSTOMER describes how many customers belong to one order. The marker next to ORDER describes how many orders belong to one customer. This is the single most confusing thing about crow's foot for beginners — the marker is on the entity it is describing the count of, which is always the entity at the other end of the line you came from.
The Four Canonical Combinations
When you combine the two ends of one relationship, you get a small number of meaningful shapes. In practice, you only need four: 1..1, 0..1, 1..N, 0..N (where 1..1 means "exactly one to exactly one" and 0..N means "zero to many"). These four cover roughly every relationship you will ever draw.
+---------------------------------------------------------------+
| THE FOUR CANONICAL RELATIONSHIPS |
+---------------------------------------------------------------+
| |
| 1..1 EXACTLY-ONE TO EXACTLY-ONE |
| USER ||----------------------------|| PROFILE |
| Every user has exactly one profile, and every profile |
| belongs to exactly one user. Both sides mandatory. |
| |
| 0..1 OPTIONAL ONE TO OPTIONAL ONE |
| USER O|----------------------------|O PROFILE |
| A user MAY have a profile, a profile MAY belong to a user. |
| (Rare in practice — usually one side is mandatory.) |
| |
| 1..N ONE-TO-MANY (parent mandatory, children optional) |
| CUSTOMER ||--------------------------O< ORDER |
| Every order has exactly one customer. A customer may have |
| zero or many orders. |
| |
| M..N MANY-TO-MANY |
| STUDENT >O------------------------O< COURSE |
| Each student takes zero or many courses, each course has |
| zero or many students. Implemented via a join table. |
| |
+---------------------------------------------------------------+Here is the part nobody tells you in school: the business rule and the schema rule are not always the same shape. You may have a "one or many" rule in your head ("a customer should always have at least one order"), but a single foreign key column on the orders table cannot enforce at least one on the customer side — only zero-or-more. To enforce true "1..N" minimum-one-on-the-parent, you need either a deferred constraint, a trigger, or an application-level check. We will look at this in the enforcement section.
Enforcing Cardinality and Ordinality in PostgreSQL
The whole point of getting cardinality and ordinality right on the ER diagram is that they translate directly into database constraints. Each of the four end markers maps onto a small set of SQL clauses. Memorize this mapping and your schemas will match your diagrams exactly.
+---------------------------------------------------------------+
| MARKER -> CONSTRAINT MAPPING |
+---------------------------------------------------------------+
| |
| || exactly one -> NOT NULL + REFERENCES |
| O| zero or one -> nullable + REFERENCES |
| ||< one or many -> NOT NULL + REFERENCES |
| (parent-side "min 1" needs trigger |
| or deferred constraint — see ex.4)|
| O< zero or many -> nullable + REFERENCES (default) |
| |
| At-most-one on the child side (1..1 or 0..1): |
| add UNIQUE on the FK column. |
| |
+---------------------------------------------------------------+Example 1 — Mandatory One-to-Many (||--O<)
The most common relationship in any schema: a child row must always belong to a parent, but a parent may have zero or many children. The order must have a customer; the customer may have zero or many orders.
-- Parent: CUSTOMER
CREATE TABLE customers (
id BIGSERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
full_name TEXT NOT NULL
);
-- Child: ORDER
-- "Each order belongs to EXACTLY ONE customer" -> NOT NULL + REFERENCES.
-- "Each customer has ZERO OR MANY orders" -> no extra constraint
-- (the default for an FK).
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL -- mandatory ordinality
REFERENCES customers(id) -- cardinality 1 on parent
ON DELETE RESTRICT,
total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
placed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Insert a customer.
INSERT INTO customers (email, full_name)
VALUES ('ada@example.com', 'Ada Lovelace')
RETURNING id;
-- id
-- ----
-- 1
-- Valid order — has a customer.
INSERT INTO orders (customer_id, total_cents) VALUES (1, 4999);
-- INSERT 0 1
-- Invalid order — NULL customer_id is rejected by NOT NULL.
INSERT INTO orders (customer_id, total_cents) VALUES (NULL, 4999);
-- ERROR: null value in column "customer_id" of relation "orders"
-- violates not-null constraint
-- Invalid order — non-existent customer is rejected by REFERENCES.
INSERT INTO orders (customer_id, total_cents) VALUES (999, 4999);
-- ERROR: insert or update on table "orders" violates foreign key
-- constraint "orders_customer_id_fkey"
-- DETAIL: Key (customer_id)=(999) is not present in table "customers".
-- A customer with zero orders is perfectly legal.
INSERT INTO customers (email, full_name)
VALUES ('grace@example.com', 'Grace Hopper');
-- INSERT 0 1Two constraints — NOT NULL and REFERENCES — produce the entire ||--O< shape. NOT NULL enforces "mandatory" on the order side. REFERENCES enforces "exactly one matching parent." The absence of any extra rule on the customer side is exactly what O< (zero or many) means.
Example 2 — Optional One-to-Many (||--O< becomes O|--O<)
Sometimes the child genuinely does not need a parent. Think of a comment that may belong to a user, but anonymous comments are also allowed. The order changes by exactly one keyword: drop NOT NULL from the FK column.
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
handle TEXT NOT NULL UNIQUE
);
-- "Each comment MAY belong to a user (or be anonymous)."
CREATE TABLE comments (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT -- nullable -> optional
REFERENCES users(id)
ON DELETE SET NULL, -- preserve anon comments
body TEXT NOT NULL,
posted_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
INSERT INTO users (handle) VALUES ('linus');
-- id 1
-- Comment from a user.
INSERT INTO comments (user_id, body) VALUES (1, 'First!');
-- INSERT 0 1
-- Anonymous comment — NULL user_id is fine.
INSERT INTO comments (user_id, body) VALUES (NULL, 'No account here.');
-- INSERT 0 1
SELECT id, user_id, body FROM comments;
-- id | user_id | body
-- ----+---------+------------------
-- 1 | 1 | First!
-- 2 | (null) | No account here.
-- Deleting the user keeps the comment, drops the link.
DELETE FROM users WHERE id = 1;
SELECT id, user_id, body FROM comments WHERE id = 1;
-- id | user_id | body
-- ----+---------+--------
-- 1 | (null) | First!Notice that ON DELETE SET NULL only makes sense when the FK column is nullable. If you tried this on the orders table from Example 1, the SET NULL action would immediately violate NOT NULL and the delete would fail. The action you choose has to match the ordinality you declared.
Example 3 — Exactly-One to Exactly-One (||----||)
A true 1..1 relationship is rarer than people think, but it shows up in vertical partitioning (splitting a wide table) and in privileged-data scenarios where one row of users always has exactly one row of user_secrets. Two changes from the one-to-many case: the FK column gets a UNIQUE constraint (so each parent has at most one child), and you have to deal with the fact that "every parent has exactly one child" cannot be enforced at table-creation time without a chicken-and-egg problem.
CREATE TABLE accounts (
id BIGSERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE
);
-- "Each account has EXACTLY ONE profile, and each profile
-- belongs to EXACTLY ONE account."
CREATE TABLE profiles (
id BIGSERIAL PRIMARY KEY,
account_id BIGINT NOT NULL UNIQUE -- mandatory + at most one
REFERENCES accounts(id)
ON DELETE CASCADE, -- profile dies with acct
display_name TEXT NOT NULL,
bio TEXT
);
INSERT INTO accounts (email) VALUES ('mary@example.com');
-- id 1
INSERT INTO profiles (account_id, display_name)
VALUES (1, 'Mary Shelley');
-- INSERT 0 1
-- Second profile for the same account is rejected by UNIQUE.
INSERT INTO profiles (account_id, display_name)
VALUES (1, 'Imposter');
-- ERROR: duplicate key value violates unique constraint
-- "profiles_account_id_key"
-- DETAIL: Key (account_id)=(1) already exists.This enforces at most one profile per account. But it does not enforce every account must have a profile — you can create an account with no profile row. To enforce "every account has exactly one profile," you need either:
- Application-level invariant: always create the profile row in the same transaction as the account row.
- Deferred constraint with a back-pointer column (see Example 4).
- A trigger that fires on
INSERT INTO accountsand inserts a stub profile row.
In real systems, option 1 is the most common — wrap the two inserts in a transaction and treat "an account without a profile" as a bug that should be impossible to reach. Database constraints catch the easy cases; transactions catch the structural ones.
Example 4 — One-or-Many on the Parent Side, Enforced with DEFERRABLE
This is the tricky one. You want to enforce "every customer has at least one order" (cardinality 1..N with mandatory parent participation). A naive FK on the orders table cannot do this, because at the moment you insert the customer, no orders exist yet. The trick is a deferrable foreign key: a back-pointer from customers to orders that is allowed to be temporarily violated within a transaction, as long as it is satisfied by COMMIT.
CREATE TABLE customers (
id BIGSERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
-- Back-pointer to a "primary" order, NOT NULL means "must have one".
primary_order_id BIGINT NOT NULL
);
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total_cents INTEGER NOT NULL
);
-- Now add the back-FK as DEFERRABLE so we can break it inside a tx.
ALTER TABLE customers
ADD CONSTRAINT customers_primary_order_fk
FOREIGN KEY (primary_order_id) REFERENCES orders(id)
DEFERRABLE INITIALLY DEFERRED;
-- A single transaction inserts both rows, and the deferred FK is
-- only checked at COMMIT.
BEGIN;
INSERT INTO customers (id, email, primary_order_id)
VALUES (1, 'alan@example.com', 1); -- primary_order_id=1 (does not exist YET)
INSERT INTO orders (id, customer_id, total_cents)
VALUES (1, 1, 9900); -- order 1 now exists
COMMIT;
-- COMMIT succeeds: at COMMIT time, both FKs are satisfied.
-- Try the same without the order — fails at COMMIT.
BEGIN;
INSERT INTO customers (id, email, primary_order_id)
VALUES (2, 'turing@example.com', 99);
COMMIT;
-- ERROR: insert or update on table "customers" violates foreign key
-- constraint "customers_primary_order_fk"
-- DETAIL: Key (primary_order_id)=(99) is not present in table "orders".Deferrable constraints are PostgreSQL's escape hatch for relationships that would otherwise be impossible to enforce at the schema level. They cost a little extra at commit time and they only work inside an explicit transaction, but they let you encode "every customer has at least one order" without falling back to triggers.
Napkin AI Visual Prompt: "Dark navy gradient (#0a0f1f -> #111a2e). Four panels in a 2x2 grid, each showing one canonical relationship as crow's-foot ER notation with sky blue (#4fc3f7) lines and rose (#ff5c8a) end markers. Top-left: USER ||---|| PROFILE labeled '1..1 exactly one'. Top-right: USER O|---|O PROFILE labeled '0..1 optional one'. Bottom-left: CUSTOMER ||---O< ORDER labeled '1..N mandatory many'. Bottom-right: STUDENT >O---O< COURSE labeled 'M..N many-to-many'. Below each panel, the matching SQL constraint snippet in white monospace (NOT NULL, UNIQUE, REFERENCES). White monospace labels throughout."
MySQL Differences
Most of what you just read works identically on MySQL with InnoDB, but there are three differences worth memorizing:
- No
DEFERRABLEconstraints. MySQL does not support deferred foreign keys. The Example 4 pattern is impossible — you have to fall back to a trigger, an application-level transaction guarantee, orSET FOREIGN_KEY_CHECKS = 0(which disables checks entirely and is dangerous). - MyISAM ignores foreign keys silently. If a table is created with the MyISAM storage engine,
REFERENCESis parsed but not enforced. Always use InnoDB (the default since MySQL 5.5) for any table with relationships. ON DELETE SET NULLrequires a nullable column in both engines, but MySQL's error message is less helpful — it points at the FK definition rather than the column. If you see "Cannot add foreign key constraint" with no obvious cause, check that the column is nullable.
For everything else — NOT NULL, UNIQUE, REFERENCES, ON DELETE CASCADE/RESTRICT/SET NULL — InnoDB behaves the same as PostgreSQL.
The Cardinality vs Ordinality Mental Model
Here is the one diagram to memorize. Every end of every relationship lives in one of four cells:
+---------------------------------------------------------------+
| THE 2x2 OF END MARKERS |
+---------------------------------------------------------------+
| |
| | cardinality 1 | cardinality N |
| -------------+-----------------+----------------- |
| OPTIONAL | O|---- | O<--- |
| (min = 0) | "zero or one" | "zero or many" |
| -------------+-----------------+----------------- |
| MANDATORY | ||---- | ||-<-- |
| (min = 1) | "exactly one" | "one or many" |
| |
+---------------------------------------------------------------+
+---------------------------------------------------------------+
| THE FOUR ENFORCEMENT RECIPES |
+---------------------------------------------------------------+
| |
| zero or one -> FK column is nullable |
| + REFERENCES parent(id) |
| + UNIQUE on the FK column |
| |
| exactly one -> FK column NOT NULL |
| + REFERENCES parent(id) |
| + UNIQUE on the FK column (for 1..1) |
| |
| zero or many -> FK column is nullable (or NOT NULL) |
| + REFERENCES parent(id) |
| + NO unique constraint |
| |
| one or many -> FK column NOT NULL |
| + REFERENCES parent(id) |
| + NO unique constraint |
| + (parent-side "min 1" needs deferred FK, |
| trigger, or app-level guarantee) |
| |
+---------------------------------------------------------------+The rule of thumb: ordinality is enforced by NOT NULL, cardinality is enforced by UNIQUE, and the relationship itself is enforced by REFERENCES. Three keywords, four shapes, one mental model.
Common Mistakes
1. Forgetting NOT NULL on a "mandatory" foreign key.
The single most common bug in beginner schemas. You draw ||--O< on the diagram, you write customer_id BIGINT REFERENCES customers(id), you forget the NOT NULL, and now your orders table happily accepts orders with customer_id = NULL. The diagram says the relationship is mandatory; the schema says it is optional. Fix: every time you write REFERENCES, ask yourself "can this column ever legitimately be NULL?" If the answer is no, add NOT NULL in the same line.
2. Modeling 1..1 with two separate tables and no UNIQUE.
You split users and user_secrets into two tables expecting a one-to-one relationship, but you forget the UNIQUE constraint on user_secrets.user_id. Now nothing stops two user_secrets rows from pointing at the same user, and your "1..1" is silently a "1..N." Fix: in any 1..1 split, the FK column on the child side must be NOT NULL UNIQUE.
3. Using ON DELETE SET NULL on a NOT NULL column.
This blows up at delete time, not create time, so it survives until production. The constraint definition is accepted, but as soon as you try to delete a parent row, PostgreSQL tries to set the child's FK to NULL, immediately violates NOT NULL, and aborts the delete. Fix: the delete action has to be compatible with the column's nullability. Use RESTRICT or CASCADE for NOT NULL FKs; SET NULL only works on nullable ones.
4. Confusing cardinality with ordinality on the diagram.
People say "one-to-many" and stop, without specifying whether the "one" side is mandatory. In a real ER diagram, "mandatory one to many" (||--O<) and "optional one to many" (O|--O<) produce different schemas with different bugs. Always specify both halves of every end: minimum and maximum. Use the 2x2 above as a checklist.
5. Trying to enforce "at least one child" with a single FK.
You want every customer to have at least one order, you put NOT NULL on orders.customer_id, and you think you are done. You are not — that constraint enforces "every order has a customer," not "every customer has an order." The latter cannot be expressed by a single FK column. Fix: use a deferrable back-pointer FK (Example 4), a trigger, or accept that the rule is an application-level invariant guaranteed by your transaction logic.
Interview Questions
1. "What is the difference between cardinality and ordinality in an ER diagram?"
Cardinality describes the maximum number of related rows on each side of a relationship — typically one or many. Ordinality describes the minimum — zero (optional participation) or one (mandatory participation). Together they answer two independent questions for each end of the relationship line: how many can there be, and is at least one required. In crow's-foot notation, the inner symbol next to the line carries cardinality (a single bar for one, a crow's foot for many), and the outer symbol next to the entity carries ordinality (an open circle for zero, a bar for one). Every relationship has two of these markers, one per end, and they are usually different on each side.
2. "Walk me through the four crow's-foot end markers and what they enforce in SQL."
The four useful end markers are: ||---- exactly one (mandatory, cardinality 1) which maps to NOT NULL plus REFERENCES; O|---- zero or one (optional, cardinality 1) which maps to a nullable FK plus REFERENCES plus UNIQUE; ||-<-- one or many (mandatory, cardinality N) which maps to NOT NULL plus REFERENCES and no unique constraint; and O<--- zero or many (optional, cardinality N) which maps to a nullable FK plus REFERENCES and no unique constraint. The mental model is that ordinality is enforced by NOT NULL, the "at most one" half of cardinality is enforced by UNIQUE, and the relationship itself is enforced by REFERENCES. Three keywords, four shapes.
3. "Why can't you enforce 'every customer has at least one order' with a single foreign key column?"
Because a foreign key on orders.customer_id only constrains the child side: it ensures that every order points to an existing customer. It says nothing about whether every customer has any orders pointing back at it. The only way to enforce "every customer has at least one order" at the schema level is to add a back-pointer column on customers (say, primary_order_id) with a NOT NULL foreign key to orders.id, declared as DEFERRABLE INITIALLY DEFERRED so you can insert both rows in a single transaction without violating the constraint mid-way. The deferred check fires at COMMIT time and only succeeds if both rows exist. MySQL does not support deferred FKs, so the same rule has to be enforced by a trigger or an application-level transaction guarantee.
4. "What is the difference between ON DELETE CASCADE, RESTRICT, and SET NULL, and which can be used with a NOT NULL foreign key?"
CASCADE deletes the child rows when the parent is deleted, RESTRICT (the default in most cases) refuses to delete the parent if any children exist, and SET NULL sets the child's foreign key to NULL when the parent is deleted, leaving the child row in place. SET NULL requires the foreign key column to be nullable, so it cannot be combined with NOT NULL — the delete would immediately violate the not-null constraint. For a mandatory relationship (NOT NULL FK), the only valid actions are CASCADE (kill the children) and RESTRICT (forbid the parent delete). The choice depends on whether the children are conceptually owned by the parent (cascade) or independent records that just reference it (restrict).
5. "How do you model and enforce a true 1..1 relationship in PostgreSQL?"
Put the foreign key on whichever side is the "weaker" entity (the one whose existence depends on the other), declare it NOT NULL UNIQUE REFERENCES parent(id), and choose ON DELETE CASCADE so the dependent row dies with the parent. The NOT NULL enforces mandatory participation on the child side, the UNIQUE enforces at-most-one (which combined with NOT NULL gives exactly one), and REFERENCES enforces the link itself. The remaining half — guaranteeing that every parent always has a child — cannot be enforced by a single FK and is usually handled by inserting both rows in the same transaction. In schemas where the rule is critical, a deferrable back-pointer FK can enforce it at COMMIT time.
Quick Reference — Cheat Sheet
+---------------------------------------------------------------+
| CARDINALITY x ORDINALITY CHEAT SHEET |
+---------------------------------------------------------------+
| |
| END MARKER | NAME | NOT NULL | UNIQUE | REFS |
| ------------+----------------+----------+--------+-------- |
| O|---- | zero or one | no | yes | yes |
| ||---- | exactly one | yes | yes* | yes |
| O<--- | zero or many | no | no | yes |
| ||-<-- | one or many | yes | no | yes |
| |
| * UNIQUE only when the OTHER side is also "one" (i.e. 1..1) |
| |
+---------------------------------------------------------------+
+---------------------------------------------------------------+
| READING A RELATIONSHIP LINE |
+---------------------------------------------------------------+
| |
| ENTITY_A <marker_A>--------<marker_B> ENTITY_B |
| |
| marker_A describes how many A rows belong to one B |
| marker_B describes how many B rows belong to one A |
| |
| Outer symbol = ordinality (minimum: zero or one) |
| Inner symbol = cardinality (maximum: one or many) |
| |
+---------------------------------------------------------------+
+---------------------------------------------------------------+
| ENFORCEMENT RECIPE BY SHAPE |
+---------------------------------------------------------------+
| |
| 1..1 parent ||----|| child |
| child.parent_id BIGINT NOT NULL UNIQUE |
| REFERENCES parent(id) ON DELETE CASCADE |
| (parent-side "must have child" -> deferred FK) |
| |
| 0..1 parent O|----|O child |
| child.parent_id BIGINT UNIQUE |
| REFERENCES parent(id) ON DELETE SET NULL |
| |
| 1..N parent ||----O< child |
| child.parent_id BIGINT NOT NULL |
| REFERENCES parent(id) ON DELETE RESTRICT |
| |
| 0..N parent O|----O< child |
| child.parent_id BIGINT |
| REFERENCES parent(id) ON DELETE SET NULL |
| |
| M..N left >O----O< right |
| CREATE TABLE left_right ( |
| left_id BIGINT NOT NULL REFERENCES left(id), |
| right_id BIGINT NOT NULL REFERENCES right(id), |
| PRIMARY KEY (left_id, right_id) |
| ); |
| |
+---------------------------------------------------------------+| Constraint | Enforces | Use When |
|---|---|---|
NOT NULL on FK | Mandatory ordinality (min 1) | Child cannot exist without parent |
| (nullable) FK | Optional ordinality (min 0) | Child may exist standalone |
UNIQUE on FK | Cardinality 1 on child side | At most one child per parent (1..1, 0..1) |
no UNIQUE | Cardinality N on child side | Many children per parent (1..N, 0..N) |
REFERENCES | The relationship itself | Always — that is what an FK is |
DEFERRABLE INITIALLY DEFERRED | Parent-side "min 1" | Need "every parent has a child" |
ON DELETE CASCADE | Lifecycle ownership | Child belongs to parent, dies with it |
ON DELETE RESTRICT | Protective parent | Forbid parent delete while children exist |
ON DELETE SET NULL | Optional links | Child survives, link is severed (FK must be nullable) |
Prev: Lesson 8.4 -- ER Diagrams Next: Lesson 9.1 -- What Is an Index?
This is Lesson 8.5 of the Database Interview Prep Course -- 12 chapters, 58 lessons.