182: Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL, and Referential Actions

Learning outcomes

By the end of this lesson, you can:

explain and apply primary keys in a realistic implementation;
explain and apply foreign keys in a realistic implementation;
explain and apply unique constraints in a realistic implementation;
explain and apply check constraints in a realistic implementation;
explain and apply not null in a realistic implementation.

Prerequisites and retrieval

This lesson assumes the 01–06 foundation and the preceding lessons in this module. Before you start, retrieve one concrete example from an earlier project where one of these concerns surfaced. Perhaps duplicate records slipped through, a child row outlived its parent, or application validation disagreed with another writer. The point is not to memorize a list of terms. It is to make a defensible choice in a PostgreSQL-backed transactional application, where schema design, correctness, query plans, and concurrency all have to work together.

Terminology

Primary keys: A primary key gives each row a stable identity. It is both unique and not null.
Foreign keys: A foreign key requires a value to refer to an existing row in another table, enforcing integrity across tables.
Unique constraints: A unique constraint enforces a business invariant such as a tenant-scoped SKU or, where appropriate, an email address.
CHECK constraints: A check constraint enforces a row-level predicate, such as requiring a nonnegative quantity or a valid date ordering.
NOT NULL: NOT NULL says that absence is not a valid state for a column. That makes the data easier to reason about and can sometimes give the optimizer useful information.
Referential actions: RESTRICT/NO ACTION, CASCADE, SET NULL, and related actions define what happens to related rows when a referenced row changes or is deleted.

These features are related, but they answer different questions. A primary key identifies a row; a foreign key connects rows; uniqueness prevents a value combination from being repeated; a check validates a row's values; and NOT NULL rules out absence. Referential actions then define the lifecycle of those relationships. Keeping those boundaries clear is useful when a write fails and you need to know which rule rejected it.

Mental model

Treat Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL, and Referential Actions as a design problem with observable inputs, outputs, invariants, and failure modes. Constraints are executable data contracts. They protect the database from every writer, including scripts, background jobs, migrations, and future services that bypass application-level validation. A strong implementation makes assumptions visible at the boundary, narrows uncertainty before data is stored, and leaves enough evidence—tests, types, constraints, metrics, or diagrams—to show why the design is safe.

A useful sequence for both an interview and a production change is:

requirement -> constraints -> model -> implementation -> failure analysis -> verification

Do not jump from a requirement straight to a library call or a migration snippet. First write down what must remain true. Then choose the mechanism that enforces that invariant, and decide what a caller should observe when the invariant is violated.

Deep dive

1. Primary keys

If a row cannot be identified reliably, updates, deletes, joins, and references all become harder to prove correct. A primary key solves that identity problem by providing a value that is unique and not null. Choose a natural key or a surrogate key deliberately. A surrogate key can make relationships and internal references convenient, but it should still coexist with a business uniqueness constraint when duplicate business records would be invalid.

Decision rule: Use a primary key deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption about identity, prefer the more explicit design.

2. Foreign keys

An order that points to a customer who does not exist is not merely an application bug; it is an invalid database state. A foreign key prevents that state by requiring the referenced row to exist and by defining cross-table integrity. Index referencing columns when joins, deletes, or updates need to find those rows efficiently. PostgreSQL does not automatically create every useful supporting index for a foreign-key column, so inspect the access patterns and add the index when the workload needs it.

Decision rule: Use a foreign key deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption about ownership or lifecycle, prefer the more explicit design.

3. Unique constraints

When a business rule says that a value or combination of values may appear only once, encode that rule in the database rather than relying on a check-then-insert sequence in application code. For example, a SKU may need to be unique within a tenant, while the same SKU can be valid for a different tenant. Understand how the engine treats NULL, and use partial or expression indexes when the uniqueness rule applies only to some rows or to a normalized expression.

This is especially important under concurrency: two requests can both observe that a value is available before either one inserts it. A unique constraint makes the final write race-safe; the application still needs to translate the resulting database error into a useful response.

Decision rule: Use a unique constraint deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption about the business key, prefer the more explicit design.

4. CHECK constraints

A check constraint is a good fit for an invariant that can be evaluated from one row using deterministic expressions. Examples include quantity >= 0 and an end date that must not precede a start date. Keeping these rules in the schema means every writer gets the same protection, regardless of which service or script performs the write.

Checks are not a substitute for rules that depend on other rows, current time in a way that must remain stable, or external systems. Those rules need a different enforcement strategy. The useful boundary is simple: a row-local predicate belongs here when the database can evaluate it reliably.

Decision rule: Use a check constraint deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding a rule that is not truly row-local, prefer the more explicit design.

5. NOT NULL

NULL is not the same thing as an empty string, zero, or false. It represents the absence of a value, and allowing it creates another state that queries and application code must handle. Use NOT NULL when absence is impossible, rather than allowing it simply because an application form happens to make a field optional. The declaration strengthens correctness reasoning and can sometimes improve the optimizer's knowledge of the data.

If absence is meaningful, model that meaning explicitly instead of removing the distinction with a default or a permissive schema. When adding NOT NULL to an existing table, account for existing invalid rows and the deployment sequence needed to bring the data into compliance.

Decision rule: Use NOT NULL deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding a genuine optional state, prefer the more explicit design.

6. Referential actions

The existence of a foreign key is only part of the relationship's design. You also need to decide what happens when the referenced row is deleted or updated. RESTRICT/NO ACTION, CASCADE, SET NULL, and related actions encode lifecycle semantics. A cascade can be appropriate when child rows have no meaning without their owner, but it is dangerous when records must be retained for audit, legal, or recovery reasons. SET NULL is only coherent when the relationship is optional and the referencing column permits nulls.

Choose the action from ownership, retention, audit, and accidental-delete requirements. Do not select CASCADE just because it makes a migration or delete statement shorter.

Decision rule: Use referential actions deliberately when they make the contract or invariant easier to prove. If the action hides an ownership or retention decision, make that decision explicit before implementing it.

Worked example

Consider a PostgreSQL-backed transactional application where schema design, correctness, query plans, and concurrency all matter. Begin by stating the requirement in one sentence. Then list the input and output contracts and identify which concept owns each failure mode. The useful separation is this: parsing and boundary validation belong at the edge; domain rules belong in the domain or service layer; persistence rules belong in the database or repository; and presentation rules belong in the client. Mixing those concerns can make a happy-path demo look shorter, but it makes edge cases much harder to explain and enforce.

The following query is not itself a constraint definition, but it gives us a realistic read path over constrained tables. It returns active customers, includes customers with no orders, counts their orders, and orders the result by that count:

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name
ORDER BY order_count DESC;

The foreign key from orders.customer_id to customers.id protects the join relationship on writes. The primary key identifies each customer and order, while NOT NULL and other row-level constraints determine which values are valid. The LEFT JOIN also makes an important read-side distinction visible: an active customer with no matching order is still returned, and COUNT(o.id) evaluates to zero for that customer.

Walk through at least four cases: the normal path, an empty or missing value, a duplicate, retry, or concurrent path where relevant, and a dependency failure. For each case, state which layer detects the problem and what the caller observes. For example, a duplicate may pass an initial application check but fail at the unique constraint during the write; a missing customer should fail the foreign-key rule; and a dependency failure should be distinguished from a constraint violation. That level of ownership and observable behavior is what a senior code review or technical interview should surface.

Production perspective

Production correctness is broader than “the code works on my machine.” Ask how the design behaves during deploys, retries, partial failure, stale clients, concurrent requests, malformed data, schema changes, and high-cardinality workloads. Prefer explicit contracts, bounded resource usage, structured errors, and measurable behavior. Optimize only after you can identify the bottleneck or risk with evidence.

When the topic involves an external dependency, define a timeout and cancellation strategy. When it involves persistence, define transaction and consistency expectations, including what happens when a constraint rejects one statement in a larger unit of work. When it involves user-visible state, define loading, empty, error, stale, and success states. When it involves security, assume the client can be modified and all network input is untrusted. Database constraints support integrity, but they do not replace authorization checks or careful handling of sensitive data.

Guided lab

Add constraints to the order schema. Include tenant-scoped uniqueness, quantity checks, foreign keys, and deliberate delete behavior. Attempt invalid writes and document which layer rejects each case. Do not stop at recording that a statement failed: capture the invariant being protected, the database error or application error observed, and how a caller should respond.

Complete the lab with this discipline:

Write the requirement and two non-requirements.
List input, output, and error contracts before implementation.
Implement the smallest correct vertical slice.
Add at least one invalid-input test and one edge-case test.
Instrument or inspect the behavior instead of guessing.
Refactor one hidden assumption into an explicit type, constraint, function, or configuration.
Explain one alternative design and why you did not choose it.
Record a short “what would break at 10× scale?” note.

Edge cases and failure modes

For each constraint type, test more than the valid example. Check absence, malformed input, duplicates, ordering or concurrency where applicable, and behavior at the smallest and largest credible sizes:

Primary keys: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes.
Foreign keys: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes.
Unique constraints: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes.
CHECK constraints: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes.
NOT NULL: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes.

The exact test is constraint-specific. A primary key raises identity and duplicate questions; a foreign key raises missing-parent and delete-behavior questions; a unique constraint raises concurrent-write and NULL semantics; a check raises boundary values and deterministic evaluation; and NOT NULL raises migration and existing-data questions. Record enough context to distinguish a rejected input from a transaction, connection, or dependency failure.

Common mistakes and debugging

Solving the example instead of the requirement: a copied pattern can be syntactically correct but architecturally wrong.
Hiding uncertainty with assertions, broad exception handlers, permissive schemas, or “temporary” any values.
Testing only the happy path and therefore discovering contracts only after integration.
Optimizing before measuring, or selecting a scalable mechanism without a scale requirement.
Letting client-side behavior stand in for server-side authorization, validation, or persistence guarantees.

For debugging, reproduce the smallest failing case first. Inspect the actual value, constraint definition, transaction state, or execution plan rather than inferring from the error message alone. Trace the boundary where the invariant first becomes false: source or build, server or route, database or query, or deployment and configuration. Then fix the layer that owns the rule instead of adding a downstream patch that merely hides the symptom.

Interview questions

What problem do primary keys solve, and what trade-off or failure mode would make you choose a different approach?
What problem do foreign keys solve, and what trade-off or failure mode would make you choose a different approach?
What problem do unique constraints solve, and what trade-off or failure mode would make you choose a different approach?
What problem do CHECK constraints solve, and what trade-off or failure mode would make you choose a different approach?
What problem does NOT NULL solve, and what trade-off or failure mode would make you choose a different approach?

Checkpoint

Without notes, explain Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL, and Referential Actions to another developer in five minutes. Include one invariant, one edge case, one production failure mode, and one alternative design. Then implement a small example without copying the lesson code. Be ready to explain why each rule belongs in the database, application, or both, and what a caller would observe when the database rejects a write.

Mastery checklist

I can define the core terms precisely.
I can choose a design from requirements instead of from habit.
I can implement and test the normal path and edge cases.
I can explain the runtime, storage, or complexity cost.
I can identify which layer owns validation, errors, and recovery.
I can compare at least two reasonable alternatives.
I can explain how the design changes at larger scale or stricter reliability.