Schema design
Lookup sheet for stage 4. The question it exists to answer: which normal form, constraint, generator or type closes this gap, and what SQLSTATE proves it closed?
The normal forms
Each form forbids a shape the one before it still allowed, and each fix is a split along the boundary the anomaly pointed at.
| Form | Forbids | Anomaly removed | Shape of the fix |
|---|---|---|---|
| First (1NF) | more than one value in a column | a LIKE or count over the packed column gives the wrong answer, since infrared matches %red% | a second table, one row per value, joined back by the original key |
| Second (2NF) | a column depending on only part of a composite key | a fact retyped on every row that shares the partial key drifts, one product described two ways | split the table on the key boundary: the part-dependent fact moves to a table keyed on the part alone |
| Third (3NF) | a non-key column depending on another non-key column | the transitive fact, reachable only by first finding the row, disagrees between rows that should share it | pull the two columns into their own table, keyed on the column the dependency actually runs from |
| Boyce-Codd (BCNF) | any determinant, key or not, that is not itself a candidate key | a non-key column decides part of the key, so a single-row update leaves two rows both "correct" and mutually impossible | split so the determinant becomes the key of its own table |
The table reads as four separate rules, and the boxes are what the sentence above it means: they nest. A schema in Boyce-Codd form satisfies the three outside it as well, so the forms are not alternatives to choose between, and reaching one never costs you an earlier one.
The stage stops at Boyce-Codd: fourth and fifth normal form concern multi-valued facts inside one relation, and neither has a violation this stage's schemas produce without a contrived example built just to show it.
Every constraint kind
The absent-value column is the one worth remembering: a constraint that reads as an obvious rule still needs an opinion about NULL, and most have none unless the schema states one.
| Kind | Sees | Absent value | SQLSTATE |
|---|---|---|---|
NOT NULL | one column, one row | rejected outright | 23502 |
CHECK | one row, every column in it | passes: the condition evaluates unknown, not false, and only false is a violation | 23514 |
UNIQUE | one table, the same column(s) on every other row | never compared: two NULLs are not equal, so any number coexist, unless declared NULLS NOT DISTINCT (PostgreSQL 15) | 23505 |
PRIMARY KEY | one table for uniqueness, one row for NOT NULL | rejected, since a primary key is UNIQUE plus NOT NULL and the second half catches it | 23502 (absent) or 23505 (duplicate) |
FOREIGN KEY | two tables | never checked: NULL satisfies the reference with no lookup performed at all | 23503 |
EXCLUDE | one table, any operator, not only equality | never compared, the same rule as UNIQUE: two rows both holding a NULL range coexist | 23P01 |
Domain CHECK | one column's value alone, no sibling column ever | passes for the identical reason a table CHECK does | 23514 |
| Generated column (degenerate case) | one row, only its other columns | not rejected at all: an absent input propagates to an absent result, since this is not a constraint | none, there is nothing to reject |
Key choice
A natural key is data the domain already has: it carries meaning and enforces uniqueness of the real-world thing it names, at no extra cost when the value is genuinely stable. A surrogate key is invented for the sole purpose of naming a row: it never has to change when the underlying fact does, so nothing downstream needs to change with it. The question is whether the identifying value is one someone outside the database has reason to edit; a country code usually is not, an email address usually is.
| Generator | What it is | One reason to pick it |
|---|---|---|
GENERATED BY DEFAULT AS IDENTITY | a sequence-backed identity that lets an explicit value bypass it | restoring old rows or migrating data that must keep its original ids, without OVERRIDING SYSTEM VALUE |
GENERATED ALWAYS AS IDENTITY | a sequence-backed identity that refuses an explicit value unless overridden | closes the collision BY DEFAULT leaves waiting; the mechanism to write in new code |
serial | sugar for an ordinary integer column with a nextval() default, not tracked as an identity | matching an already-established schema written before identity columns existed; not for new code |
gen_random_uuid() | sixteen bytes of pure randomness, no extension required | a value that must be generated outside the database, by a client, before the row exists |
uuidv7() | a timestamp-ordered UUID, new in PostgreSQL 18 | keeps rows inserted together near each other in the key's own order, where a random UUID scatters them |
Whichever wins, every other candidate key still needs its own UNIQUE constraint: a primary key only enforces uniqueness of the column it names.
Referential actions
RESTRICT and NO ACTION both refuse, but under different SQLSTATEs: RESTRICT checks immediately and ignores a DEFERRABLE clause entirely, while NO ACTION checks at the end of the statement, or the end of the transaction if declared DEFERRABLE INITIALLY DEFERRED, which lets a delete and its repair coexist inside one transaction.
| Action | What it does | SQLSTATE when it refuses |
|---|---|---|
NO ACTION | refuses the delete or update if a reference would dangle; the unwritten default | 23503 |
RESTRICT | refuses the same way, but checked immediately, never deferred | 23001 |
CASCADE | performs the same delete or update on every referencing row | does not refuse; turns one write into several |
SET NULL | writes NULL into the referencing column, unless that column is itself NOT NULL, in which case that separate constraint fires first | 23502 if the column is NOT NULL, otherwise none |
SET DEFAULT | writes the column's declared DEFAULT, or NULL if none was written, since an undeclared default is NULL | none immediately; 23503 later if the default value itself stops existing |
Type choice
| Decision | Refuses | Changes silently | Can never take back |
|---|---|---|---|
numeric(p,s) against double precision | numeric refuses a value whose whole part will not fit after rounding to scale, 22003 | numeric rounds excess scale, 10.005 becomes 10.01; double precision changes every decimal fraction to its nearest binary approximation | double precision cannot recover exact decimal equality: 0.1 + 0.2 = 0.3 is false, and the error compounds over many additions |
varchar(n) against char(n) | varchar(n) refuses a value past its length, 22001 | char(n) pads silently to its declared width, invisible through length() and a ::text cast | nothing: the padding round-trips through octet_length() and format(); the type just makes it easy to forget it is stored |
timestamptz against timestamp | neither refuses a value | timestamptz converts the literal's offset away, keeping only the instant; timestamp drops the offset entirely, keeping only the digits | timestamp cannot take back which zone the writer meant, so the same digits mean a different instant to a reader in another zone |
native enum against text with CHECK against a lookup table | all three refuse an unlisted label, under 22P02, 23514 and 23503 respectively | none of the three changes a value | enum can never remove a label once added, 0A000, and cannot use one just added in the same transaction, 55P04; the other two reverse both operations as ordinary statements |
text[] as a tag list | refuses nothing about one element's spelling | nothing changes silently | a foreign key can never reach one element: naming the array against a scalar key fails at CREATE TABLE itself, 42804 |
Denormalisation
Four shapes of a second copy, ordered by who is responsible for keeping it honest.
| Case | Who keeps it correct | The audit it needs |
|---|---|---|
| Generated column | the database, on every write, or every read if left VIRTUAL (the PostgreSQL 18 default when neither keyword is written) | none: it cannot disagree with the row it is computed from, since it only ever sees that row |
| Trigger-maintained column | a trigger, only for the writes to the table it watches | a drift query, a LEFT JOIN back to the source rows with HAVING stored <> count(...), since a direct UPDATE to the column bypasses the trigger entirely and a bounding CHECK cannot notice |
| Materialised view | nobody, until something runs REFRESH | a refresh-log row recording when the view was last true, since staleness between refreshes is otherwise invisible to a reader |
| Plain duplicated column, copying a fact another table still owns | nobody, ever, automatically | a scheduled query joining back to the source and flagging any row IS DISTINCT FROM it, since nothing at insert time can compare two tables at once |
A column such as price_at_sale looks identical to the last row on the page and needs none of that machinery, because it is not a copy of the same fact at all: it names what something cost at a moment now past, and a live current_price was never going to agree with it. The test is whether the two columns were ever supposed to say the same thing.
The diagnostics of the stage
Every error a lesson quoted, re-run and confirmed.
| Error | SQLSTATE | Cause |
|---|---|---|
null value in column "..." violates not-null constraint | 23502 | a NOT NULL column, or a primary key's implicit one, received no value |
new row for relation "..." violates check constraint | 23514 | the CHECK condition evaluated to false; NULL evaluates unknown and passes instead |
value for domain ... violates check constraint | 23514 | a domain's CHECK behaves exactly like a table CHECK |
duplicate key value violates unique constraint | 23505 | UNIQUE or a primary key found the same value, or pair of values, already present |
insert or update on table "..." violates foreign key constraint | 23503 | a referencing value has no match on the referenced side, or never did |
update or delete on table "..." violates foreign key constraint | 23503 | the referenced row still has children, under NO ACTION or a SET DEFAULT whose default row is itself gone |
update or delete on table "..." violates RESTRICT setting of foreign key constraint | 23001 | the same shape, but RESTRICT's own code, checked immediately even inside a deferred transaction |
there is no unique constraint matching given keys for referenced table | 42830 | a foreign key named only part of a composite key, which identifies nothing alone |
cannot insert a non-DEFAULT value into column "..." | 428C9 | an identity column declared ALWAYS refuses an explicit value without OVERRIDING SYSTEM VALUE |
column "..." can only be updated to DEFAULT | 428C9 | a generated column refuses a direct write, since only its own expression may supply one |
cannot use subquery in column generation expression | 0A000 | a generated column's expression may only see its own row, never another table |
data type text has no default operator class for access method "gist" | 42704 | an EXCLUDE constraint, or WITHOUT OVERLAPS, on a non-range column needs btree_gist before GiST has an operator class to use |
conflicting key value violates exclusion constraint | 23P01 | an EXCLUDE constraint, or the WITHOUT OVERLAPS shorthand built on one, found an existing row for which every paired condition holds |
constraint using WITHOUT OVERLAPS needs at least two columns | 42601 | the shorthand pairs a range against another column that must also match; one column alone is meaningless |
invalid input value for enum ... | 22P02 | a label outside a native enum's fixed list |
unsafe use of new value "..." of enum type | 55P04 | a label added by ALTER TYPE ... ADD VALUE cannot be read by an insert in the same transaction |
dropping an enum value is not implemented | 0A000 | a native enum can never lose a label once added |
foreign key constraint "..." cannot be implemented | 42804 | a foreign key names an array column against a scalar key; the types are incompatible before any row exists |
value too long for type character varying(n) | 22001 | varchar(n) refuses a value past its declared length outright, never truncates |
numeric field overflow | 22003 | a numeric(p,s) value's whole part will not fit once rounded to the declared scale |
materialized view "..." has not been populated | 55000 | the view was built WITH NO DATA and nothing has run REFRESH yet; the HINT says so |
cannot refresh materialized view "..." concurrently | 55000 | REFRESH ... CONCURRENTLY needs a unique index with no WHERE clause first; the same SQLSTATE, a different HINT |
The review method
Lesson 27 runs one procedure against a schema built to fail every way this stage taught, then again against the fix. Its five questions, in order: what a row means and whether the key actually says so; which column can be absent and what absence means there; which rule lives only in a comment or in application code rather than in the schema; which fact appears twice, and who keeps the two copies equal; and, last, what today's schema would accept that the domain forbids. See Lesson 27 for the schema that answers each one by running the insert rather than reading the column list and guessing.
What does not travel to SQLite
SQLite applies type affinity rather than type enforcement, so numeric(12,2) keeps every digit of 10.005 rather than rounding it, and never raises an overflow error for a value too large. GENERATED ... AS IDENTITY, CREATE DOMAIN, EXCLUDE USING gist, WITHOUT OVERLAPS, CREATE MATERIALIZED VIEW and UNIQUE NULLS NOT DISTINCT are all syntax errors, with no equivalent spelling. A generated column is one of the few things that does travel: SQLite has supported GENERATED ALWAYS AS (...), with the same STORED and VIRTUAL forms, since long before PostgreSQL made VIRTUAL the default, and a virtual and a stored column read back identically there too.