Evgeny comes down from his floor — phone already turned screen-first toward you: a signage mockup, BREW-NEVA where BREW-NORTH used to be.

Evgeny

Rebranding. The second location hasn't even opened yet, and Emil has already signed off: NEVA, not NORTH. In the database that's just one UPDATE, right? By Monday.

Y

Sounds about right. One row in shops, an UPDATE by code…

Dmitry

What references that code?

Plenty does. Brew's shops table got a human-readable code as its primary key: BREW-CENTRAL, BREW-NORTH. Reasonable — the code is short, meaningful, easy to look up by. But stock levels and order line items reference that code via foreign keys, and "renaming" here means changing the value of the primary key: cascade-updating the code across every referencing table, while reports that cache the old code start pointing into the void. "That's just one UPDATE" is exactly the "just" Botyr warned about on day one. The pain isn't in the rename — it's in making a mutable business code the row's identity.

This unit has two goals. First: what PRIMARY KEY actually is — NOT NULL plus UNIQUE in one declaration (so the key is never empty and never repeats). Second: when to key on a natural code (a business value) versus a surrogate id (synthetic, meaningless). Short answer: surrogate for identity, the natural code as a separate UNIQUE column for lookups.

PRIMARY KEY = NOT NULL + UNIQUE

PRIMARY KEY isn't a separate "column type" but a pairing of two constraints. By declaring code TEXT PRIMARY KEY you automatically got NOT NULL (without even writing it) and UNIQUE. So you can't put NULL into a PK column — that's not_null_violation (SQLSTATE 23502) — and you can't repeat a value — unique_violation (23505). These two invariants are what make a row addressable: for any key value there's exactly one row, or none.

NOT NULL also works on its own, on a regular column: name TEXT NOT NULL catches a missing required field with the same 23502. It's the first line of validation — in the schema, not in app code — so it can't be bypassed by a forgotten check or a buggy client.

Natural vs surrogate key

A natural key is a business value that's already unique: a shop code, a book's ISBN, a phone number. The plus — no extra column, the key is "meaningful." The minus that shot Brew in the foot: business values change, and changing a primary key is painful — foreign keys and caches hold onto it.

A surrogate key is a synthetic id (GENERATED ALWAYS AS IDENTITY from 02-01) that means nothing beyond "this particular row." The business code doesn't disappear — it lives in a separate column with UNIQUE (still convenient to look up by). Renaming the code now doesn't touch the id: foreign keys reference the stable id and don't break. The cost — an extra column and index. For most application tables this is the right default; a natural key is good where the value is truly immutable (an ISO 3166 country code).

Which key to use

What our code shows

Two tables (DDL in schema.sql): one on a natural key, the other on a surrogate with a UNIQUE code. Note: NOT NULL on code in shop_natural isn't written — PRIMARY KEY imposed it:

CREATE TABLE shop_natural (
    code  TEXT  PRIMARY KEY,          -- PK ⇒ NOT NULL + UNIQUE automatically
    name  TEXT  NOT NULL
);
CREATE TABLE shop_surrogate (
    id    BIGINT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code  TEXT    NOT NULL UNIQUE,    -- business code is an attribute, not identity
    name  TEXT    NOT NULL
);

query.sql hits the PK invariants and shows the contrast on rename:

-- name: InsertNaturalNullCode :exec     -- NULL into PK → 23502
INSERT INTO shop_natural (code, name) VALUES (NULL, $1);
 
-- name: RenameNaturalCode :exec         -- changes the KEY VALUE itself
UPDATE shop_natural SET code = $2 WHERE code = $1;
 
-- name: RenameSurrogateCode :exec       -- changes an attribute, id unchanged
UPDATE shop_surrogate SET code = $2 WHERE code = $1;

main.go renames BREW-OLD → BREW-NEW in both tables. In the natural one it checks: the old key is gone, a new one appeared — the key itself moved. In the surrogate one — that id before and after is the same: only the code attribute changed. Errors are printed as SQLSTATE (the code is deterministic, the text is not).

Running it

docker compose up -d
make lecture L=02-schema-and-constraints/02-02-not-null-pk-natural-vs-surrogate T=db-reset
make lecture L=02-schema-and-constraints/02-02-not-null-pk-natural-vs-surrogate

Output:

1) PRIMARY KEY = NOT NULL + UNIQUE (таблица на натуральном ключе code):
   NULL в PK-колонку code      → отклонён: SQLSTATE 23502 (not_null_violation)
   дубль code 'BREW-CENTRAL'   → отклонён: SQLSTATE 23505 (unique_violation)
2) NOT NULL на обычной колонке:
   NULL в name                 → отклонён: SQLSTATE 23502 (not_null_violation)
3) Переименование ключа 'BREW-OLD' → 'BREW-NEW':
   натуральный PK (code):  старого ключа нет (false), новый есть (true) — сменилось само значение ключа
   суррогат (id):          id = 1 → 1 неизменен, сменился только атрибут code — identity строки стабильна

(The demo prints in Russian.) The PK batted away both NULL (23502) and a duplicate (23505); NOT NULL on name is the same 23502. And block 3 shows the key point: the natural key changed entirely on rename (BREW-OLD gone, BREW-NEW appeared — every foreign key was supposed to "move" with it), whereas the surrogate id stayed 1 and only the code attribute changed. That's exactly what spares you Brew's pain.

The fence

What we simplified: we presented the surrogate as an "almost always right default" but didn't finish the foreign-key story — no table here references our code/id, so the rename cascade stayed off-screen (we'll do it in 02-03). In production the choice of key is a trade-off your DBA and you both keep in mind:

• A surrogate decouples identity from the business value (the recommended default for app tables) but adds a column and an index and requires remembering that "real" uniqueness lives on the UNIQUE code, not the id.
• A natural key is good for genuinely immutable values (currency, country codes) and in many-to-many junction tables, where a composite natural PK is natural (our base table inventory (shop_id, drink_id) is exactly that).
• Always put NOT NULL explicitly on business-required fields: let the schema, not the code, ensure data doesn't arrive empty.

Takeaways

• PRIMARY KEY is NOT NULL + UNIQUE: the key is never NULL (23502) and never repeats (23505); NOT NULL makes the schema the first line of validation.
• A natural key = a business value as identity: it changes with the business, and changing it "drags" the foreign keys along.
• A surrogate key = a synthetic id owns identity, the business code lives in a UNIQUE column; renaming the code doesn't touch the id. This is the right default for most app tables.
• A composite natural key fits junction tables (inventory (shop_id, drink_id) in the base schema).

Next up — the 02-03 "Foreign keys (ON DELETE CASCADE/SET NULL)" unit: how an FK enforces referential integrity (a dangling reference → 23503) and what to do with children when a parent is deleted — cascade-delete them, null out the reference, or forbid the delete entirely.