Part 6 · 9 chapters · ~20 min

DDL, constraints, the schema as a contract

A schema is not a container for data; it is a set of promises about what the data can be. Every constraint you decline to declare becomes a rule enforced somewhere less reliable — in application code, in a code review, or nowhere. This part is about choosing types precisely and making the database hold the line.

Numeric types, and the money rule

Type	Storage	Exact?	Use for
`TINYINT`…`BIGINT`	1–8 bytes	Yes	Counts, ids, flags
`DECIMAL(p,s)`	~4 bytes per 9 digits	Yes	Money, anything that must add up
`FLOAT` / `DOUBLE`	4 / 8 bytes	No	Measurements, science, coordinates

never store money in a float

Binary floating point cannot represent 0.1 exactly, so errors accumulate across arithmetic. A ledger that is off by fractions of a cent is a ledger that fails reconciliation, and the discrepancy is untraceable because every individual operation looked correct.

Use DECIMAL(19,4), or store integer minor units — pence, kobo, cents — in a BIGINT. The integer approach is what most payment systems do, and it makes the rounding policy explicit rather than implicit.

run it

CREATE TABLE m (f DOUBLE, d DECIMAL(19,4));
INSERT INTO m VALUES (0.1, 0.1);

-- add 0.1 ten times over
SELECT SUM(f), SUM(d) FROM (
  SELECT f, d FROM m UNION ALL SELECT f, d FROM m
  UNION ALL SELECT f, d FROM m UNION ALL SELECT f, d FROM m
  UNION ALL SELECT f, d FROM m UNION ALL SELECT f, d FROM m
  UNION ALL SELECT f, d FROM m UNION ALL SELECT f, d FROM m
  UNION ALL SELECT f, d FROM m UNION ALL SELECT f, d FROM m
) t;
--   DOUBLE  → 0.9999999999999999
--   DECIMAL → 1.0000

-- and the comparison that famously fails
SELECT 0.1 + 0.2 = 0.3;                          -- depends on types!
SELECT CAST(0.1 AS DOUBLE) + CAST(0.2 AS DOUBLE) = 0.3;   -- 0

Dates, times, and time zones

Type	Stores	Time zone behaviour
`DATE`	Calendar date	None
`DATETIME` (MySQL)	Wall-clock date and time	None — stored and returned verbatim
`TIMESTAMP` (MySQL)	UTC internally	Converted to the session time zone on read
`timestamptz` (Postgres)	UTC internally	Converted on read. The right default.
`timestamp` (Postgres)	Wall clock	None — despite the name, it is not zone-aware

the MySQL TIMESTAMP 2038 problem

MySQL's TIMESTAMP is a 32-bit seconds count, so it cannot represent anything after 19 January 2038. A subscription end date, a mortgage maturity, a retention policy — any future date past 2038 silently fails or clamps. Use DATETIME (year 9999) and store UTC explicitly.

the rule that avoids most date bugs

Store UTC, convert at the edges. The database holds UTC; the application converts to the user's zone for display and back for input. Never store local time without its offset, because the offset is not recoverable — daylight saving means one local wall-clock hour occurs twice a year.

The exception is genuinely zone-less data: a birthday, a public holiday, a contract date. Those are DATE and converting them is the bug.

code

-- intervals: date arithmetic without manual second-counting
SELECT NOW() + INTERVAL 30 DAY;
SELECT DATE_ADD(d, INTERVAL 1 MONTH);      -- handles month lengths
SELECT TIMESTAMPDIFF(DAY, a, b);

-- and the sargability rule from Part 9 applies to dates constantly:
WHERE DATE(created_at) = '2026-09-21' -- no index
WHERE created_at >= '2026-09-21'
AND created_at <  '2026-09-22' -- index works

Strings and collations

A string column carries three separate decisions: length, character set, and collation. The third is the one that surprises people, because it silently governs comparison and sorting.

Type	Behaviour	Note
`CHAR(n)`	Fixed length, right-padded	Trailing spaces are added on write and stripped on read. Use only for genuinely fixed codes.
`VARCHAR(n)`	Variable, length-prefixed	The default choice.
`TEXT` / `LONGTEXT`	Variable, stored off-page	Costs an extra page read per row (MySQL module, ch 21).

collation decides equality, not just sort order

Under utf8mb4_0900_ai_ci — accent- and case-insensitive — 'café' = 'CAFE' is TRUE. That is usually what you want for names and search, and definitely not what you want for passwords, tokens, or case-sensitive identifiers. Those want utf8mb4_bin.

And mixing collations across a join disables the index silently (MySQL module, ch 106). Pick one per database and enforce it.

Constraints and their cost

Constraint	Guarantees	Write cost
`NOT NULL`	A value is present	Free, and it removes three-valued logic from that column
`PRIMARY KEY`	Unique and not null	An index you needed anyway
`UNIQUE`	No duplicates	An index plus a lookup per write. Note: multiple NULLs allowed (ch 10).
`FOREIGN KEY`	Referential integrity	A lookup per write, plus locks on the parent
`CHECK`	An arbitrary predicate	Expression evaluation per write. Enforced in MySQL since 8.0.16.

code

CREATE TABLE orders (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  reference   VARCHAR(32)  NOT NULL UNIQUE,
  customer_id BIGINT NOT NULL,
  total       DECIMAL(19,4) NOT NULL,
  status      VARCHAR(16)  NOT NULL DEFAULT 'pending',
  created_at  DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

  CONSTRAINT fk_customer FOREIGN KEY (customer_id)
    REFERENCES customers(id) ON DELETE RESTRICT,
  CONSTRAINT ck_total CHECK (total >= 0),
  CONSTRAINT ck_status CHECK (status IN ('pending','paid','shipped','cancelled'))
);

the argument for constraints in the database

"We validate in the application" holds only while the application is the sole writer. It never stays that way: a migration script, an admin console, a data fix run at 2am, a second service, an analyst with write access. Constraints in the database hold regardless of who is writing.

The honest counter-argument is at very high write volume, where foreign key checks add latency and lock the parent row — which is why several large systems drop them deliberately. That is a measured trade with a written rationale, not a default.

Foreign key actions

Action	On parent delete/update	Use when
`RESTRICT` / `NO ACTION`	Reject the operation	The safe default. Forces the caller to decide.
`CASCADE`	Delete/update the children too	True ownership — order lines with their order
`SET NULL`	Null the child's reference	Optional association — an employee's former manager
`SET DEFAULT`	Set the column default	Rare; not supported by InnoDB

CASCADE deletes more than you think

Cascades chain. Deleting one customer can cascade to orders, then to order lines, then to shipments — potentially millions of rows in a single transaction, holding locks throughout, generating enormous undo, and never appearing in any query log because it is one small DELETE statement.

For anything with real volume, prefer RESTRICT and delete explicitly in batches. You then control the order, the batch size, and the transaction boundaries.

Foreign keys also take locks on the parent row during child writes, which can create contention on a popular parent — and deadlocks when two transactions touch parents in different orders (MySQL module, ch 60).

Deferrable constraints

The standard allows a constraint to be checked at COMMIT rather than per statement. This is what makes circular references and bulk reordering possible.

code

-- Postgres
CREATE TABLE t (
  pos INT UNIQUE DEFERRABLE INITIALLY IMMEDIATE
);

BEGIN;
SET CONSTRAINTS ALL DEFERRED;
UPDATE t SET pos = pos + 1;    -- transiently violates UNIQUE
COMMIT;                       -- checked here, and it holds

MySQL has no deferrable constraints

Every constraint is checked immediately. So reordering a uniquely-indexed column requires a workaround: update to negative or offset values first, then to the targets; or drop the index, update, and recreate it — which is a table rebuild on a large table.

MySQL does have SET FOREIGN_KEY_CHECKS=0, useful for bulk loads, but it disables checking rather than deferring it — no validation happens at commit, so invalid rows persist silently. Only use it on data you have already validated.

Generated columns

A column whose value is computed from other columns, maintained by the database so it cannot drift out of sync.

code

-- VIRTUAL: computed on read, costs no storage
ALTER TABLE orders
  ADD COLUMN total_inc_vat DECIMAL(19,4)
      AS (total * 1.20) VIRTUAL;

-- STORED: computed on write, occupies space, can be indexed everywhere
ALTER TABLE users
  ADD COLUMN email_lower VARCHAR(255)
      AS (LOWER(email)) STORED,
  ADD UNIQUE INDEX idx_email_lower (email_lower);
--   ← case-insensitive uniqueness, enforced by the database

	VIRTUAL	STORED
Storage	None	Occupies space
Computed	On read	On write
Indexable	Secondary indexes only	Anywhere
Best for	Cheap expressions, indexed lookups	Expensive expressions read often

The main uses: extracting a JSON field into an indexable column (MySQL module, ch 107), normalizing case for a unique constraint, and making a non-sargable expression indexable without changing every query.

Domains, enums, and modelling with types

Approach	Adding a value	Verdict
`ENUM` (MySQL)	`ALTER TABLE`; appending is instant, inserting in the middle rebuilds	Compact, but the ordering is by definition order which surprises people, and `ENUM` values compare as integers in some contexts.
`CHECK (x IN (...))`	`ALTER TABLE` to change the constraint	Portable and explicit. Good default.
Lookup table + FK	`INSERT` a row	Best when the set changes, or when values need attributes like a label or sort order.
`DOMAIN` (Postgres)	Alter the domain once, everywhere	A named reusable type with its own constraints.

the decision rule

If the set of values is genuinely fixed by the code — order status, boolean-ish flags — a CHECK constraint is fine and self-documenting. If the set is data that a human might change, or values need a display label, ordering, or an active flag, it is a table. The common mistake is modelling business-owned categories as an ENUM and then needing a deploy to add one.

Migration as a language problem

The application and the schema deploy at different moments, and for a window both versions run simultaneously. A migration is safe only if every intermediate state works for both.

Expand and contract

code

1. EXPAND    add the new structure, nullable, alongside the old
2. deploy    application writes BOTH, reads the old
3. backfill  copy existing data, in batches
4. deploy    application reads the NEW, still writes both
5. deploy    application stops writing the old
6. CONTRACT  drop the old structure

Slower than one ALTER, and it is the only approach that survives a rollback at any step. Each stage is independently reversible.

Operation	Safe online?	Note
Add a nullable column	Yes	Instant in MySQL 8 (module ch 100)
Add a column with a default	Yes in 8.0+	Was a full rebuild pre-8.0
Add an index	Usually	`ALGORITHM=INPLACE, LOCK=NONE`
Drop a column	Yes in 8.0.29+	But deploy code that stops referencing it first
Rename a column	No	Breaks running code instantly. Expand/contract instead.
Narrow a type	No	Rebuild plus possible data loss
Add `NOT NULL`	No	Requires validating every row. Backfill, then constrain.

the rename that takes down production

ALTER TABLE users RENAME COLUMN email TO email_address completes in milliseconds and breaks every running instance of the old code immediately — during the deploy, while both versions are live. It is fast, which is exactly what makes it dangerous.

Renames are always expand/contract: add the new column, dual-write, backfill, switch reads, stop writing the old, drop it. Six steps instead of one, and none of them cause an outage.

Part 7 covers modifying data: upserts and their races, the patterns that stay correct under concurrency, and where SQL's DML has sharp edges.