Relational Modeling: PK/FK/UNIQUE/CHECK/NULL

1. First screen: a database can accept rows and still reject the domain

Imagine a small shop with customers, products, and orders. The application creates numeric IDs, checks email format in a form, refuses negative prices in the UI, and looks up a customer before creating an order. Everything appears fine until another import path bypasses those checks. The database then accepts a duplicate customer email, a negative product price, or an order that points to no customer at all.

This lesson owns one bounded question: which domain invariants belong in the relational model, and how do you prove that the database enforces them? The target artifact is Relational Constraint Model Record v1. For each important invariant you will record the candidate database constraint, one allowed example, one rejected example, the observed database result, the domain rationale, and any residual rule that still belongs outside the database.

The core mechanism set is deliberately small: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NULL/NOT NULL. The goal is not to memorize syntax. The goal is to connect each mechanism to a specific invariant and then use positive and negative probes as evidence. A diagram or clean CREATE TABLE statement is not enough.

2. Route orientation: choose a route, keep the same evidence bar

Foundation route. Start with the invariant matrix. State the rule in plain language before writing SQL. Use the worked shop example and run one violation at a time.

Repair route. Start from a failed model. If duplicate email is the defect, do not redesign every table. Predict what should be rejected, repair the smallest relevant constraint, rerun the probe, and preserve the observed evidence.

Experienced route. Skip introductory syntax, but do not skip the hard parts: candidate-key choice, foreign-key action rationale, PostgreSQL NULL behavior under UNIQUE, the CHECK/NOT NULL boundary, mutation evidence, and changed-context transfer.

All routes finish with the same artifact and the same independent transfer task. AI may suggest candidate SQL only after you state the invariant; it may not invent database output or make the final modeling decision for you.

3. Mental model: invariant → column set → constraint → probes → evidence

Relational constraints are useful when they are attached to an explicit invariant.

1. State the invariant. Example: “every order belongs to an existing customer.”
2. Identify the owning table and columns. Here the rule crosses orders.customer_id to customers.id.
3. Choose the smallest database mechanism that can enforce that rule. Here that is a foreign key.
4. Write one accepted probe. An order referencing an existing customer must succeed.
5. Write one rejected probe. An order referencing a missing customer must fail.
6. Record observation separately from inference. The database error is observation; “therefore referential integrity is enforced for this path” is an inference bounded to the tested schema.
7. State the residual rule. Authorization, business workflow, or the product’s correct delete policy may still live elsewhere.

This sequence prevents a common failure: choosing a familiar SQL keyword first and inventing the invariant afterward.

4. Foundation layer: five contracts, five different jobs

Primary Key — row identity

A PRIMARY KEY makes the chosen column set unique and non-null. PostgreSQL allows composite primary keys, but a table has only one primary-key constraint. [PG18-CONSTRAINTS] The important modeling decision is not “always use an integer ID”; it is which row identity the table must expose to other relational rules. A surrogate key may be convenient while a separate business identifier still needs UNIQUE.

Foreign Key — referential integrity

A FOREIGN KEY requires referencing values to match an accepted referenced key. [PG18-CONSTRAINTS] It prevents an orphan reference, but it does not decide the product’s correct ON DELETE or ON UPDATE behavior. RESTRICT, NO ACTION, CASCADE, and other actions encode different domain consequences. The foreign key creates the integrity boundary; the action still requires a product decision.

UNIQUE — candidate-key uniqueness

UNIQUE protects a candidate key or combination. PostgreSQL treats nulls as distinct by default for uniqueness, so multiple nulls can coexist; NULLS NOT DISTINCT changes that behavior. [PG18-CONSTRAINTS] Do not generalize PostgreSQL’s default to every SQL implementation.

CHECK — row-local value rule

A CHECK expression protects a row-local Boolean condition such as price > 0. In PostgreSQL, a check succeeds when the expression is true or unknown; a null input can therefore pass a check such as price > 0. [PG18-CREATE-TABLE] If null itself is invalid, add NOT NULL as a separate contract.

NULL / NOT NULL — optionality

Nullability is a domain statement. NOT NULL means the value must exist. Allowing NULL means absence is a legitimate state, not merely “we forgot to validate it.” Optional shipment tracking is a good example: an unshipped order may genuinely have no tracking code yet.

5. Deep explanation: constraints do not all prove the same kind of correctness

A primary key proves only identity properties of the chosen key. It does not prove that a customer email is unique, that a price is possible, or that an order points to a real customer. Those are separate invariants and need separate constraints.

A foreign key gives database-enforced referential integrity, but only for the declared relationship. It does not prove that a referenced customer is authorized to place the order, that the customer is active, or that deleting a customer should cascade into order deletion. Those are different rules. If you choose ON DELETE CASCADE, that choice must come from the domain, not from convenience.

UNIQUE becomes subtle when nullable values are involved. Suppose orders.tracking_code is optional but, when present, must identify only one order. PostgreSQL’s default unique behavior fits that contract: many orders may have NULL tracking codes, but two rows may not share the same non-null code. If the domain instead allows at most one null, NULLS NOT DISTINCT changes the contract. The lesson is not “one syntax is better”; the lesson is to make null semantics explicit.

CHECK is similarly easy to overclaim. CHECK (price > 0) prevents negative or zero non-null prices, but it does not by itself reject NULL because an unknown result is not false. Therefore a required positive price needs both price numeric NOT NULL and CHECK (price > 0). [PG18-CREATE-TABLE]

Finally, PostgreSQL documentation warns against using CHECK as a general cross-row or cross-table assertion. This lesson keeps checks row-local. Cross-row business invariants, migration behavior, isolation/concurrency, and performance are adjacent scopes, not hidden objectives here. [PG18-CONSTRAINTS]

6. Worked example: customers, products, and orders

Start from these domain rules:

• customer row identity is customers.id;
• customer email is required and unique;
• product row identity is products.id;
• product SKU is required and unique;
• product price is required and greater than zero;
• each order references an existing customer;
• tracking code is optional, but any non-null tracking code must be unique.

A bounded PostgreSQL model can express those rules:

CREATE TABLE customers (
    id bigint PRIMARY KEY,
    email text NOT NULL UNIQUE,
    display_name text NOT NULL
);

CREATE TABLE products (
    id bigint PRIMARY KEY,
    sku text NOT NULL UNIQUE,
    price numeric(12,2) NOT NULL CHECK (price > 0)
);

CREATE TABLE orders (
    id bigint PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    tracking_code text UNIQUE
);

Now prove rather than admire it. Insert a valid customer and product. Insert a valid order. Then try a duplicate email, a negative price, a null price, and an orphan customer_id. Each rejected case answers a different question. Finally insert two orders with NULL tracking codes and verify that PostgreSQL’s default uniqueness semantics allow both, then try the same non-null tracking code twice and verify the second insert is rejected.

The evidence record must not say “constraints work.” It should say exactly which probe was run, what the database returned, what that observation supports, and what it does not prove.

7. Failure modes: five shortcuts to reject

“We already have a primary key, so the model is safe.” Row identity does not protect every business invariant. Duplicate emails and impossible prices can still enter.

“The foreign key tells us what happens on delete.” It does not. The relationship and referential action are separate decisions.

“UNIQUE means the same thing around NULL everywhere.” It does not. Record the PostgreSQL behavior you actually rely on and mark the portability boundary.

“CHECK(price > 0) means price is required.” It does not in PostgreSQL. Null can make the check expression unknown, which is accepted; NOT NULL is a distinct contract. [PG18-CREATE-TABLE]

“Database constraints replace application validation.” They do not replace authorization, workflow, good user-facing validation, or rules the database cannot safely own. Database constraints are the last line for invariants the database can own.

8. Experienced bridge: model candidate keys and null behavior intentionally

Experienced learners should make three decisions explicit instead of hiding them behind syntax.

First, separate row identity from business identity. A surrogate id may be the primary key while email, sku, or another natural identifier remains a candidate key protected by UNIQUE.

Second, treat referential actions as domain choices. For historical orders, ON DELETE RESTRICT may be safer than cascade; another domain may make the opposite choice. This lesson requires the rationale, not a universal default.

Third, name the null-bearing uniqueness contract. “Tracking code is optional, but unique when present” is different from “only one row may have no tracking code.” PostgreSQL can express both behaviors, so choosing one without stating the invariant is incomplete modeling.

Do not expand this exercise into indexing strategy, query planning, migrations, transaction isolation, or concurrency. Those concerns matter, but they require their own evidence.

9. Alternatives and tradeoffs: choose by invariant, not fashion

A surrogate primary key + UNIQUE business key keeps references stable while preserving a business uniqueness rule. A natural primary key can be appropriate when the business identifier is stable, compact, and truly identity-bearing. Neither choice proves the rest of the model.

A foreign key can reference a primary key or another acceptable unique key, but the referenced key must actually represent the relationship you intend. A technically valid foreign key can still encode the wrong domain relationship.

For a required bounded numeric value, NOT NULL and CHECK solve different parts of the rule. Combining them is often correct when both presence and range matter. For optional values, forcing NOT NULL merely to simplify code can misrepresent the domain.

The modeling test is simple: can you state the invariant, show the chosen constraint, and produce both an allowed and a rejected case whose result directly supports that choice?

10. Guided and independent practice: DAT001-LAB1

The lab starter intentionally omits critical constraints. Your job is not to “make tests green” by copying a finished schema. Work in this order:

1. Read model-contract.json and the broken starter schema.
2. Fill the invariant matrix before editing SQL.
3. Create your own repaired solution.sql in the learner workspace.
4. Run the positive and negative PostgreSQL probes.
5. Record actual database outcomes in Relational Constraint Model Record v1.
6. Predict a failure mutation before running it.
7. Repair only the failed invariant and rerun.
8. Complete the support-ticket changed-context transfer independently.

The starter checks verify that the exercise is intact without supplying the repaired schema. Build solution.sql yourself and use your own PostgreSQL observations as evidence.

11. Verification and evidence: what “done” means

For each critical dimension, record:

• Invariant — the domain rule in one sentence.
• Candidate constraint — the precise database mechanism.
• Allowed example — a row or operation expected to succeed.
• Rejected example — a row or operation expected to fail.
• Observed DB result — actual PostgreSQL outcome, not a prediction.
• Domain rationale — why this mechanism owns the invariant.
• Residual rule — what still requires application/workflow/human logic.

At least one deliberate mutation must be predicted, observed, diagnosed, repaired, and rerun. Negative evidence matters because one successful insert cannot reveal whether invalid states remain possible.

12. Assessment and transfer: a support-ticket domain

Changed context: organizations have many members and many support tickets. Membership must refer to an existing organization and user. A ticket has a required subject. An external reference is optional but must be unique when present. Some ticket-closing permissions remain an application authorization rule.

Without copying the shop SQL, choose row keys and candidate keys, identify each foreign key, decide nullability, define any row-local checks, and state which rules remain outside the database. Then provide one accepted and one rejected probe for each critical invariant. Your final conclusion must distinguish observation from inference and state one residual limitation.

13. References and freshness

• [PG18-CONSTRAINTS] PostgreSQL 18 documentation, 5.5 Constraints, official primary source; reviewed 2026-09-14; review due 2026-12-31.
• [PG18-CREATE-TABLE] PostgreSQL 18 documentation, CREATE TABLE, official primary source; reviewed 2026-09-14; review due 2026-10-17.

Version-specific behavior is bounded to PostgreSQL 18/current documentation. Re-review when the cited PostgreSQL documentation changes, a review-due date arrives, or the executable lab no longer matches current PostgreSQL behavior.