Schema design and constraints

A constraint is a statement about what is true, enforced by the one component every client goes through. Application validation is a request; a constraint is a fact. That difference matters the day a script, a migration or a second service writes to the table.

Keys

A primary key identifies a row: unique and not null. Two schools:

• Natural key — data that is already unique, like email or an ISO country code. Fewer columns, and no join needed to see what a row is. It breaks when the “unique” thing changes, and people change their email.
• Surrogate key — a generated id with no meaning. Stable forever, at the cost of one extra column and a join to say anything about it.

Use a surrogate key and put a UNIQUE constraint on the natural one. That gets both properties: a stable identity for foreign keys to point at, and a database-enforced guarantee that the business rule holds.

CREATE TABLE customer (
  id     bigserial PRIMARY KEY,     -- surrogate: what foreign keys reference
  email  text NOT NULL UNIQUE       -- natural: what the business considers unique
);

Generating the id:

Prefer IDENTITY over serial in new PostgreSQL schemas: serial creates a sequence that is only loosely attached to the column, which shows up when you drop or copy the table.

A random UUID as a primary key is a real cost in MySQL specifically, and the next lesson explains why: InnoDB stores the table in primary key order, so random keys scatter writes across the whole file. UUID_TO_BIN(uuid, 1) reorders the timestamp bits to make them roughly sequential and exists for exactly this reason.

Foreign keys, and what happens on delete

CREATE TABLE order_item (
  order_id bigint NOT NULL REFERENCES "order"(id) ON DELETE CASCADE,
  product_id bigint NOT NULL REFERENCES product(id) ON DELETE RESTRICT
);

The choice is not stylistic. ON DELETE CASCADE from an order to its items is right — an item has no meaning without its order. ON DELETE CASCADE from a quiz to its attempts is a mistake, because deleting a quiz would silently take every result anyone ever got with it. This site hit exactly that and the answer was not a different referential action: it was to stop deleting. Content that is withdrawn moves to draft and keeps its children.

That is the general lesson. Before choosing CASCADE, ask what the child rows are evidence of. If they are records of something that happened, they should outlive the thing they point at.

MySQL note: InnoDB silently creates an index on a foreign key column if one does not exist. PostgreSQL does not, and an unindexed foreign key makes every parent delete scan the child table. Add the index yourself.

NOT NULL and CHECK

NOT NULL is the cheapest documentation in a schema, and it removes a whole class of the NULL problems from lesson 2. Default to it, and make nullability a decision you can justify — shipped_at is nullable because “not shipped yet” is a real state.

ALTER TABLE "order" ADD CONSTRAINT ck_order_status
  CHECK (status IN ('new', 'paid', 'shipped', 'cancelled'));

MySQL before 8.0.16 accepted CHECK and ignored it. It parsed, it appeared in the DDL, and it enforced nothing. If you inherit a schema from that era, do not assume the constraints listed in it are constraints. From 8.0.16 they are enforced.

Name your constraints. ck_order_status in an error message tells you what broke; the generated name order_status_check1 makes you go and look.

Modelling a closed set of values

Three options, and the trade-off is real:

-- 1. CHECK constraint. Portable, needs a migration to add a value.
status text NOT NULL CHECK (status IN ('new', 'paid', 'shipped'))

-- 2. Native enum.
--    PostgreSQL: CREATE TYPE order_status AS ENUM ('new','paid','shipped');
--    MySQL:      status ENUM('new','paid','shipped') NOT NULL
--    Compact, and awkward to change. MySQL's ENUM is also numeric underneath, so
--    reordering the list rewrites the meaning of existing rows.

-- 3. Lookup table plus a foreign key.
CREATE TABLE order_status (code text PRIMARY KEY, label text NOT NULL);
-- status text NOT NULL REFERENCES order_status(code)

The lookup table wins whenever the set has attributes — a label to display, a sort order, an active flag. CHECK wins for a small set that genuinely never grows. Native enums are the option people reach for first and regret third.

Types that cause bugs

Money

Never float or double. Binary floating point cannot represent 0.1, so totals drift and two runs disagree.

PostgreSQL’s numeric has arbitrary precision; MySQL’s DECIMAL is fixed at declaration. Both are exact, which is the only property that matters here.

Timestamps

This is the type mistake that costs the most.

MySQL’s TIMESTAMP does the right conversion and dies in 2038 because it is a 32-bit epoch. DATETIME has the range but no zone, so two servers in different zones write different values for the same instant. The workable discipline in MySQL is: store UTC in DATETIME, set time_zone = '+00:00' on every connection, and convert only for display.

Text

PostgreSQL: use text. There is no performance difference between text and varchar(n), so a length limit should exist only when it is a real rule.

MySQL: VARCHAR(n) needs the length, and it interacts with indexes — an index has a key-length limit (3072 bytes on InnoDB with DYNAMIC row format), and utf8mb4 counts 4 bytes per character, so VARCHAR(1000) cannot be fully indexed.

And the charset trap: MySQL’s utf8 is not UTF-8. It is a three-byte subset that cannot store emoji or many CJK characters, and inserting one either errors or truncates depending on strict mode. The real thing is utf8mb4. It is the default from MySQL 8.0; anything older, or migrated from older, needs checking.

JSON

jsonb is genuinely useful for data whose shape you do not control. It is not a substitute for columns: you lose type checking, NOT NULL, foreign keys and cheap statistics. If you know the field exists, make it a column.

Generated columns

Both engines can compute a column from others, which turns a repeated expression into something indexable.

-- PostgreSQL (stored only)
ALTER TABLE order_item
  ADD COLUMN line_cents integer GENERATED ALWAYS AS (unit_cents * quantity) STORED;

-- MySQL (STORED or VIRTUAL)
ALTER TABLE order_item
  ADD COLUMN line_cents INT AS (unit_cents * quantity) STORED;

MySQL’s VIRTUAL computes on read and still supports a secondary index, which makes it the standard workaround for two things MySQL lacks: expression indexes and partial indexes.

Normalisation, briefly and practically

The forms have names, and the working version is shorter:

1. One value per column. No comma-separated lists in a text field.
2. Every non-key column depends on the whole key. If half your columns only depend on part of a composite key, they belong in another table.
3. No column depends on another non-key column. Storing country and country_name together means they can disagree.

The failure normalisation prevents is not wasted space — it is two copies of one fact that drift apart. Denormalise deliberately, when a measurement says a join is too expensive, and then own the job of keeping the copies consistent. That is what a materialised view or a generated column is for.

What to take away

• Surrogate primary key, UNIQUE on the natural key.
• Choose the referential action from what the child rows are evidence of. Records of events should outlive their parent.
• PostgreSQL does not index foreign keys for you. MySQL does.
• MySQL below 8.0.16 ignored CHECK entirely.
• Money in integer minor units or DECIMAL, never float.
• timestamptz in PostgreSQL; in MySQL, UTC in DATETIME with a fixed session zone.
• MySQL’s utf8 is not UTF-8. Use utf8mb4.