PostgreSQL DDL and Constraint-First Schema Design

1. First screen: a DDL file can parse and still hide the real contract

A team already has customers and orders. The next change adds an external order reference, a positive amount, a status default, and a customer relationship. One developer starts from table shape and writes whatever ALTER TABLE statements make the columns exist. Another starts from the invariants: which values must exist, which combinations must be unique, which references must resolve, which old rows must be checked, and which operational claims cannot be made from a small fixture.

This lesson owns the second approach. The target artifact is Constraint-First PostgreSQL DDL Record v1. For every important invariant, you will record the exact DDL statement, a stable constraint name where the mechanism supports one, the expected effect on existing data, a positive probe, a negative probe, the observed database result, the source authority, and one operational caveat.

The bounded goal is not “write a migration.” It is translate an already-bounded invariant model into reviewable PostgreSQL DDL and evidence. Full rollout sequencing, rollback, online migration strategy, concurrency design, and zero-downtime claims belong to later work.

2. Route orientation: different scaffolds, one evidence bar

Foundation route. Read the invariant worksheet first. For each row, identify the owning column set, choose the PostgreSQL constraint mechanism, name it, and only then write the DDL. Use the guided orders example before the independent transfer.

Repair route. Start from one failed contract. If DEFAULT 'pending' is being treated as a status constraint, isolate that mistake, predict an invalid value that would still be accepted, add the missing constraint, and rerun the probe. Do not redesign unrelated DDL.

Experienced route. Compress syntax review. You still must show named constraints, distinguish direct validation from staged validation, keep NOT VALID within the PostgreSQL 18 constraint types that support it, provide mutation evidence, and state what your local result does not prove.

All routes finish with the same artifact. AI may suggest candidate statements after you state the invariant, but it may not invent execution output or decide the final invariant ownership for you.

3. Mental model: invariant → DDL form → name → existing-data path → probes → evidence

Constraint-first DDL reverses a common order of work. Instead of beginning with CREATE TABLE or ALTER TABLE, begin with the rule you are trying to preserve.

1. State the invariant in domain language. “Every order amount is present and greater than zero.”
2. Choose the database mechanism. Requiredness maps to NOT NULL; positivity maps to CHECK (amount > 0).
3. Choose CREATE TABLE or ALTER TABLE for the real state you have. New structure and existing populated structure have different evidence questions.
4. Name maintainable constraints. A name makes review, error diagnosis, later DROP/VALIDATE, and change discussion traceable. Naming does not make the semantics correct; it makes the contract referable. [PG18-CONSTRAINTS]
5. Decide how existing rows are treated. A constraint added to an existing table may validate current rows immediately, or for supported constraint types may be added NOT VALID and validated later. [PG18-ALTER-TABLE]
6. Run both positive and negative probes. A successful valid row proves something different from a rejected invalid row.
7. Separate observation from operational inference. “VALIDATE CONSTRAINT failed because one old row violated the check” is an observation. “This change is safe for every production workload” is an unsupported deployment claim.

This sequence turns DDL into a reviewable contract instead of a pile of syntax.

4. Foundation layer: five ideas that keep schema changes honest

DDL is a contract surface

CREATE TABLE declares a table and its constraints; ALTER TABLE changes an existing definition. [PG18-CREATE-TABLE] [PG18-ALTER-TABLE] The syntax is not the outcome. The outcome is that an explicit invariant is represented by a database mechanism and backed by evidence.

Constraint names are part of maintainability

PostgreSQL lets you provide names for constraints. Named constraints give reviewers and operators a stable handle for failures and later changes. [PG18-CONSTRAINTS] Prefer names that identify the table, protected rule, and mechanism, such as orders_amount_positive_ck or orders_customer_fk. Do not encode temporary ticket numbers or environment names into canonical constraint names.

DEFAULT and constraint mean different things

A default supplies a value when a write uses the default; it does not generally restrict later values. PostgreSQL documents that changing a default affects subsequent writes and does not rewrite existing rows. [PG18-ALTER-TABLE] Therefore status text DEFAULT 'pending' does not prevent status='nonsense' or an explicit NULL. If the allowed status set is exactly pending/paid/cancelled, the column needs both requiredness and an allowed-values constraint. PostgreSQL CHECK treats TRUE or UNKNOWN as satisfied, so CHECK (status IN (...)) alone does not reject NULL. [PG18-CONSTRAINTS]

Existing data changes the question

When a new constraint is added to a populated table, current rows matter. PostgreSQL 18 allows NOT VALID on foreign-key, CHECK, and not-null constraints; the initial scan is skipped, but new or updated rows are still checked. The database does not treat the constraint as valid for all rows until VALIDATE CONSTRAINT succeeds. [PG18-ALTER-TABLE]

That path is not a universal switch. UNIQUE and PRIMARY KEY are not in that NOT VALID list. If uniqueness must be added to existing data, handle its existing-data evidence honestly and do not paste NOT VALID onto unsupported syntax.

Operational behavior is subcommand-bounded

ALTER TABLE does not have one lock/scan profile. PostgreSQL explicitly documents that lock level differs by subform and that the strictest required lock applies when subcommands are combined. [PG18-ALTER-TABLE] This lesson records the exact subcommand and its documented caveat. It does not convert a local quick run into a production availability guarantee.

5. Deep explanation: staged validation is evidence, not a magic migration recipe

Suppose orders already contains rows and you want to add CHECK (amount > 0). A normal validated add inspects existing data as part of adding the constraint. If an old row violates the rule, the statement fails.

For supported types, PostgreSQL provides a staged path. Adding the constraint with NOT VALID skips the potentially long validation scan of old rows, while still enforcing the new constraint for later inserts or updates. Later, VALIDATE CONSTRAINT scans existing rows and marks the constraint valid only if those rows satisfy it. [PG18-ALTER-TABLE]

This distinction is valuable because it makes three states visible:

• constraint absent — neither old nor new rows are protected by that mechanism;
• constraint present but not yet valid for all existing rows — new changes are enforced, old rows still require validation;
• constraint validated — PostgreSQL has checked existing rows for that constraint.

The lesson uses this state transition as evidence. It does not prescribe a production rollout. Real deployment planning must additionally consider workload, transaction duration, indexes, table size, competing locks, maintenance windows, replication, rollback, and other concerns outside this lesson.

PostgreSQL 18 also makes a useful lock boundary explicit: VALIDATE CONSTRAINT acquires SHARE UPDATE EXCLUSIVE; other ALTER TABLE subforms may require stronger locks, and ADD FOREIGN KEY has its own documented lock behavior. [PG18-ALTER-TABLE] The correct lesson conclusion is “check the exact subcommand,” not “ALTER TABLE is safe” or “NOT VALID is lock-free.”

6. Worked example: turn an orders invariant sheet into named DDL

Assume the foundational relational model already exists. The customers table has its identity and email rules. The orders table has columns but is missing the new contract:

• customer_id must be present and reference an existing customer;
• external_ref must be present and unique;
• amount must be present and greater than zero;
• status must be present; it defaults to pending, but allowed values are only pending, paid, or cancelled;
• one old row deliberately contains a negative amount so validation behavior can be observed.

A constraint-first patch can be written in stages:

ALTER TABLE orders
  ALTER COLUMN customer_id SET NOT NULL,
  ALTER COLUMN external_ref SET NOT NULL,
  ALTER COLUMN amount SET NOT NULL,
  ALTER COLUMN status SET NOT NULL;

ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers(id) NOT VALID;

ALTER TABLE orders
  ADD CONSTRAINT orders_external_ref_uq UNIQUE (external_ref);

ALTER TABLE orders
  ADD CONSTRAINT orders_amount_positive_ck CHECK (amount > 0) NOT VALID;

ALTER TABLE orders
  ADD CONSTRAINT orders_status_ck
  CHECK (status IN ('pending','paid','cancelled')) NOT VALID;

The important point is not to copy this block blindly. Before running it, predict the existing-data effect of each statement. status SET NOT NULL is necessary because the allowed-values CHECK does not reject NULL by itself. The unique constraint does not have the same staged NOT VALID path as the check and foreign key. The negative old amount should let the CHECK ... NOT VALID statement be installed, but later validation should fail until that old row is repaired.

After repairing the deliberately invalid row, validate only the constraints that were added not-valid:

ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;
ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_positive_ck;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_ck;

Now prove the contract with explicit negative probes: orphan customer_id, null customer_id, duplicate and null external_ref, zero/negative/null amount, unsupported status, and null status. Record the actual error/result from a real PostgreSQL run. Do not fill the evidence record from memory.

7. Failure modes: five shortcuts that look efficient and destroy evidence

Shape-first DDL. Columns are created first and invariants are “added later.” The later step is where requiredness, uniqueness, existing data, and validation semantics are usually forgotten.

Anonymous maintained constraints. PostgreSQL can generate names, but a maintained schema benefits from stable names when errors, review comments, DROP, or VALIDATE operations need to refer to the exact rule. [PG18-CONSTRAINTS]

DEFAULT used as validation. A default chooses a value for omitted input; it does not make other values illegal. A status default does not replace a status constraint.

NOT VALID treated as “optional forever.” New/updated rows are still enforced for supported constraint types, and the database does not assume all rows satisfy the constraint until validation succeeds. [PG18-ALTER-TABLE]

One blanket operational claim for all ALTERs. Lock and scan behavior depends on the exact subcommand and version. Record the statement you actually use and defer production rollout guarantees.

8. Experienced bridge: optimize reviewability, not cleverness

Experienced database engineers can compress syntax, but the record should become more precise, not shorter in evidence.

Use a naming convention that is deterministic enough for reviewers to predict. Keep names semantic rather than implementation-ticket-specific. Separate constraints that protect different invariants even when PostgreSQL syntax lets you combine multiple subcommands.

When existing data is involved, write the precondition explicitly. A direct ADD CONSTRAINT ... UNIQUE has different applicability from ADD ... CHECK ... NOT VALID. If a staged path is unavailable for the chosen mechanism, say so instead of inventing syntax.

Also separate semantic correctness from operational acceptability. A DDL statement can correctly encode the invariant and still need a different deployment plan for a large or highly concurrent production table. That does not make the schema contract wrong; it means a later migration/concurrency decision still owns unanswered operational questions.

9. Alternatives and trade-offs: choose the mechanism that matches the invariant and state

Declare in CREATE TABLE vs add with ALTER TABLE. New empty structures can usually express the full contract at creation. Existing populated structures force an additional existing-data question.

Direct validated add vs staged NOT VALID path. Direct validation closes the contract in one step when current data is already clean and the operational context permits it. For PostgreSQL 18 foreign-key, check, and not-null constraints, NOT VALID can separate installation from existing-row validation. [PG18-ALTER-TABLE]

Database constraint vs application-only validation. Application validation can provide better UX and workflow context. It does not replace database enforcement for invariants that must survive every write path. Conversely, not every product rule belongs in a database constraint.

One big ALTER vs explicit small statements. Combining subcommands can be convenient, but PostgreSQL uses the strictest required lock among combined subcommands. [PG18-ALTER-TABLE] During review, separate statements can make evidence and operational caveats easier to reason about. This lesson does not prescribe one deployment style universally.

10. Guided and independent practice

Guided pass

Use the provided orders fixture. Before editing SQL, complete the invariant worksheet with: invariant, owning columns, mechanism, constraint name, existing-data expectation, positive probe, negative probe, and operational caveat.

Then implement the DDL patch. Run the static checks. In a real local PostgreSQL runtime, capture the result of installing the constraints, one rejected new bad amount while the amount CHECK is still not-valid, one failed validation caused by the deliberate old bad row, the repair, successful validation, and the full named violation-probe set.

Repair pass

Delete one named constraint from your patch or replace the status check with only a default. Predict which invalid write becomes admissible. Run the probe, restore the constraint, and preserve both results in the evidence record.

Independent pass

Complete the billing transfer without copying the orders SQL. The billing invariant is: invoice numbers are unique within a tenant, amount is required and positive, and each invoice references an existing tenant. Decide the constraint set and state which operational questions remain outside this lesson.

11. Verification and evidence: what counts as proof here

A complete Constraint-First PostgreSQL DDL Record v1 contains:

1. the domain invariant in plain language;
2. the exact DDL statement or bounded snippet;
3. the stable constraint name where applicable;
4. what should happen to existing rows;
5. one valid probe and its observed result;
6. one invalid probe and its observed result;
7. the source ID for any version-sensitive semantic claim;
8. a clear split between observation and inference;
9. a residual operational limitation.

A parsed SQL file is not enough. A screenshot without the SQL and probe is not enough. A generated explanation that says “PostgreSQL will reject this” is not runtime evidence. The observation field stays unfilled until a real PostgreSQL execution supplies it.

12. Assessment and transfer

Retrieval. Explain why constraint-first DDL begins from invariants rather than table shape, and name one thing this lesson does not prove.

Application. Implement the bounded orders patch and produce a complete DDL record with positive and negative evidence.

Diagnosis. A teammate adds status text DEFAULT 'pending' and claims invalid statuses are impossible. Produce the smallest counterexample and repair.

Decision. Defend either direct validation or a staged supported NOT VALID path for one specific constraint, while refusing to turn that choice into a zero-downtime guarantee.

Transfer. For a billing schema, enforce tenant-scoped invoice number uniqueness and positive amount. Produce a new record independently and list migration/concurrency questions that must be deferred.

13. References and freshness

• [PG18-CREATE-TABLE] — PostgreSQL 18 CREATE TABLE reference. Used for current table/constraint declaration syntax.
• [PG18-CONSTRAINTS] — PostgreSQL 18 constraints documentation. Used for constraint semantics and naming.
• [PG18-ALTER-TABLE] — PostgreSQL 18 ALTER TABLE reference. Used for existing-data validation and subcommand-bounded lock/scan behavior.

Freshness rechecked on 2026-09-14 against PostgreSQL 18 documentation. The source boundary remains exactly these three official references. Re-review is due 2026-10-17 or earlier if PostgreSQL documentation or the lab behavior changes.