Reading Constraints and Schema Contracts

The boss says she wants you to add a new table to the Budget Tracker. Subscriptions: who is paying for what monthly service, how much, and when it renews.

The agent writes a CREATE TABLE statement. You glance at it. Most of it is column names and types. Three lines toward the bottom catch your eye: a FOREIGN KEY, a UNIQUE, and a CHECK. Those three lines are doing more work than the entire rest of the schema. They are the contract the database promises to enforce against every future write, including the ones the agent has not written yet.

If you can read those three lines, you can predict which inserts will fail before anyone tries them.

Key Terms for This Lesson

CREATE TABLE: The statement that defines a table's columns, types, and rules.
PRIMARY KEY: The unique identifier for each row. Usually id. Cannot be NULL. Must be unique.
FOREIGN KEY: A pointer to another table's primary key. The database refuses to create a row that points to a non-existent target.
UNIQUE: No two rows can share this value. The database refuses duplicates.
NOT NULL: This column must have a value. The database refuses NULL.
CHECK: A boolean condition that every row must satisfy. The database refuses rows that fail it.

A Schema You Can Read

CREATE TABLE subscriptions (
    id           SERIAL PRIMARY KEY,
    user_id      INTEGER NOT NULL REFERENCES users(id),
    service_name TEXT    NOT NULL,
    amount       NUMERIC(10, 2) NOT NULL CHECK (amount > 0),
    renews_at    DATE    NOT NULL,
    UNIQUE (user_id, service_name)
);

Walk it line by line.

id SERIAL PRIMARY KEY: an auto-incrementing integer that uniquely identifies each row.
user_id INTEGER NOT NULL REFERENCES users(id): must be filled in, and must point to a real user. No orphan subscriptions.
service_name TEXT NOT NULL: must be filled in.
amount NUMERIC(10, 2) NOT NULL CHECK (amount > 0): a money value with exact precision, must be filled in, and must be greater than zero. No $0 or negative subscriptions.
renews_at DATE NOT NULL: must be filled in.
UNIQUE (user_id, service_name): each user can have at most one row per service. No duplicate Netflix entries for Alice.

That schema, in business terms: every subscription belongs to a real user, has a non-empty service name, has a positive amount, has a renewal date, and a user cannot be listed twice for the same service.

You did not write a single line of application code. The database does the work.

What This Schema Refuses

Predict the outcome of these inserts before you read the answers.

Insert	Outcome	Why
`INSERT (user_id=9999, service_name='Netflix', amount=15, renews_at='2026-06-01')`	Rejected	user 9999 does not exist (FK)
`INSERT (user_id=1, service_name=NULL, amount=15, ...)`	Rejected	service_name is NOT NULL
`INSERT (user_id=1, service_name='Netflix', amount=-5, ...)`	Rejected	CHECK (amount > 0) fails
`INSERT (user_id=1, service_name='Netflix', amount=15, ...)` (twice)	Second one rejected	UNIQUE (user_id, service_name)
`INSERT (user_id=1, service_name='Netflix', amount=15, ...)` (Alice's first)	Accepted	All constraints pass

The schema is doing five different validation checks for free. Application code never needs to write any of them. As long as the schema is the gate, the data stays clean.

Constraint Recognition Cheat Sheet

Constraint	Sentence in English
`PRIMARY KEY`	Identifies the row. Unique and not NULL.
`REFERENCES other_table(col)`	Must point to a real row in `other_table`.
`NOT NULL`	Must have a value.
`UNIQUE`	No two rows can share this value.
`UNIQUE (col1, col2)`	No two rows can share this combination.
`CHECK (condition)`	Must satisfy this boolean condition.
`DEFAULT value`	If not supplied, use this value.
`ON DELETE CASCADE`	If the referenced row is deleted, delete this one.
`ON DELETE SET NULL`	If the referenced row is deleted, NULL this one.

Memorise the words. The grammar is small and it does not change.

PRIMM-AI+ Practice: Read the Subscriptions Schema

Predict [AI-FREE]

The agent produced this schema for the new subscriptions feature.

CREATE TABLE subscriptions (
    id           SERIAL PRIMARY KEY,
    user_id      INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    service_name TEXT    NOT NULL,
    amount       NUMERIC(10, 2) NOT NULL CHECK (amount > 0),
    renews_at    DATE    NOT NULL,
    UNIQUE (user_id, service_name)
);

Before running anything, write down:

Five inserts that this schema should accept.
Five inserts that this schema should refuse.
What happens to a subscription row when its user is deleted.
One ambiguous case where you would push back on the agent (for example: should the amount really be required to be positive, or could it be zero?).
Your confidence score from 1 to 5.

Run

Ask Claude Code to run the table creation inside a rollback wrapper. Confirm the CREATE succeeds and the final statement is ROLLBACK.

Use this shape so the practice table does not remain in your database after the exercise:

BEGIN;

CREATE TABLE subscriptions (
    id           SERIAL PRIMARY KEY,
    user_id      INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    service_name TEXT    NOT NULL,
    amount       NUMERIC(10, 2) NOT NULL CHECK (amount > 0),
    renews_at    DATE    NOT NULL,
    UNIQUE (user_id, service_name)
);

-- Run your accepted and rejected inserts here.

ROLLBACK;

If your SQL editor cannot keep the transaction open across statements, ask the agent to use CREATE TEMP TABLE subscriptions_practice (...) instead. The rule is the same: practice DDL must not leave a permanent table behind.

Then attempt your five rejected inserts, one at a time, and read the error messages. You should see clear violations for each:

ERROR:  insert or update on table "subscriptions" violates foreign key constraint
ERROR:  null value in column "service_name" violates not-null constraint
ERROR:  new row for relation "subscriptions" violates check constraint
ERROR:  duplicate key value violates unique constraint

Each error names the constraint that did its job. Read the error. Match it to your prediction.

Investigate

Write your own explanation:

The FOREIGN KEY rejects orphan subscriptions because the database checks that user_id exists in users before inserting.
The NOT NULL rejects partial rows because the column has no default and no value was supplied.
The CHECK rejects negative amounts because the boolean condition amount > 0 is false.
The UNIQUE rejects the second insert because the combination of user_id and service_name already exists.

Then ask the agent:

"What is the difference between ON DELETE CASCADE and ON DELETE SET NULL here?"
"If I want to allow $0 amounts (for free trials), what is the smallest change to the schema?"
"If I want to allow two Netflix subscriptions per user (one work, one personal), what is the smallest change to the UNIQUE constraint?"

Modify

Change one requirement: the boss wants to track whether a subscription is currently active or paused. Predict the new column and the constraints it should have. Then ask the agent for the smallest possible ALTER TABLE to add that column.

Read the change. Confirm the new column has a sensible default and an appropriate NOT NULL or CHECK.

Make [Mastery Gate]

Write a one-sentence brief for a new small table in the Budget Tracker. Something like a tags table, or a currencies table. Hand it to the agent. Ask for the CREATE TABLE.

Read the schema. List, in plain English, the inserts the schema will accept and the inserts it will refuse. Approve or reject.

The gate passes when your list of expected rejections matches the constraints you read in the schema, and when you can articulate a missing constraint the schema should have added.

A Light Preview: Row Level Security

You may see policies in agent output that look like this.

CREATE POLICY user_isolation ON expenses
  FOR SELECT
  USING (user_id = current_setting('app.user_id')::int);

In plain English: "When anyone reads from expenses, only show rows where the user_id matches the current session's user." This is Row Level Security, or RLS. It is how Postgres lets a single database safely serve multiple tenants.

You do not need to design RLS in this chapter. Your approval job here is recognition: when CREATE POLICY, USING, and WITH CHECK appear in agent-written SQL, you should know that the database is enforcing row boundaries, not just storing rows.

Try With AI

Prompt 1: Schema-to-English

Here is a CREATE TABLE statement. Translate it into a paragraph of
plain English that names every business rule it enforces, then
list five inserts it would accept and five it would reject.

CREATE TABLE subscriptions (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  service_name TEXT NOT NULL,
  amount NUMERIC(10, 2) NOT NULL CHECK (amount > 0),
  renews_at DATE NOT NULL,
  UNIQUE (user_id, service_name)
);

What you're learning: Translating a schema into business rules is the core reading skill of this lesson. Doing it out loud once builds the habit. After that, you do it in your head.

Prompt 2: Constraint Gap Hunt

Look at this schema and tell me three business rules a careful designer
would want to enforce but that this schema currently does not enforce.
Propose the constraints that would close each gap.

CREATE TABLE expenses (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  category TEXT,
  amount NUMERIC(10, 2),
  date DATE
);

What you're learning: Reading the schema for what is missing. Notice that user_id is nullable (no NOT NULL). amount has no CHECK. There is no UNIQUE on anything. These are gaps. Spotting them is the senior version of the reading skill.

Prompt 3: Match Error to Constraint

For each error message below, tell me which constraint type produced it
and write a one-sentence explanation of what the row tried to do.

A) ERROR: insert or update on table "subscriptions" violates foreign key constraint "subscriptions_user_id_fkey"
B) ERROR: duplicate key value violates unique constraint "subscriptions_user_id_service_name_key"
C) ERROR: new row for relation "subscriptions" violates check constraint "subscriptions_amount_check"
D) ERROR: null value in column "renews_at" of relation "subscriptions" violates not-null constraint

What you're learning: The error messages name the constraint and the column. Reading them is part of reading the schema. By the end of this drill, you can debug a failed insert without opening the schema file.

Checkpoint

I can read a CREATE TABLE statement and list the inserts it will refuse.
I can recognise FOREIGN KEY, UNIQUE, NOT NULL, and CHECK by sight.
I can spot missing constraints in an under-protected schema.
I have seen what a CREATE POLICY statement looks like and can describe its row-boundary purpose.

A Schema You Can Read​

What This Schema Refuses​

Constraint Recognition Cheat Sheet​

PRIMM-AI+ Practice: Read the Subscriptions Schema​

Predict [AI-FREE]​

Run​

Investigate​

Modify​

Make [Mastery Gate]​

A Light Preview: Row Level Security​

Try With AI​

Prompt 1: Schema-to-English​

Prompt 2: Constraint Gap Hunt​

Prompt 3: Match Error to Constraint​

Checkpoint​

Flashcards Study Aid​