Part 1 — The relational foundation › Chapter 02

The relational model: sets, keys and NULL

Why a table is a set and not a list, what a key really guarantees, and where NULL came from.

From the advert

“Progettazione e modellazione di basi dati relazionali” — you can read a paragraph of business description and produce tables and keys that make the wrong state impossible to write down. That skill is this chapter and the four after it.

A table is a set, not a list

🧒 Simple

A table is like a bag of cards, not a stack of them. Nobody promises the cards come out in the order you put them in, and asking “what is the third card?” is not a real question. If order matters, one of the things written on each card has to say so.

🎓 Professional

A relation is an unordered set of tuples over a heading. Three practical consequences, all of which are tested:

There is no row order without ORDER BY. A query that appears sorted is sorted by accident — because the plan happened to use an ordered index. Add a parallel scan or a different index and the accident stops happening, in production, on a Friday.
There is no “row number” a row carries. If position is part of the business meaning — line 1, line 2, line 3 of an order — it is a column (line_no), not an artefact of storage.
Duplicate rows are meaningless. A true set has none. SQL permits them, which is the language's most famous deviation from the model, and it is why UNION deduplicates while UNION ALL does not.

⚠ Trap

“It came back in the right order when I tested it.” This is the single most expensive false belief in SQL. Without ORDER BY the engine is free to return rows in any order it likes, and it will change its mind when the data grows, when statistics update, or when it decides to go parallel. If the order is part of the answer, say so.

What a key actually guarantees

A candidate key is a minimal set of columns whose values are unique across the whole relation. “Minimal” is load-bearing: if (email) is unique then (email, id) is unique too, but it is not a candidate key because you could remove a column and still be unique.

Term	What it means	What it guarantees
Candidate key	Minimal unique identifier	No two rows share these values
Primary key	The candidate key you nominated	Unique and `NOT NULL`; usually decides physical order
Alternate key	A candidate key you did not nominate	Enforce it with `UNIQUE` anyway, or it is not enforced
Foreign key	Values must exist in another relation's key	Referential integrity — no order line pointing at a deleted order
Natural key	Made of real-world data (VAT number, ISBN)	Meaningful, and changes when the real world does
Surrogate key	Meaningless, engine-generated	Stable forever, and tells you nothing

🎓 Natural or surrogate? The answer that gets you hired

Both. A surrogate primary key for stability and narrow foreign keys, plus a UNIQUE constraint on the natural key so the business rule is still enforced. Choosing a surrogate key instead of declaring the natural key unique is the actual mistake, and it is extremely common: you get an id column and two rows for the same customer. Say that sentence in an interview and you will sound like someone who has cleaned up the aftermath — because you will have described it.

A surrogate key for the plumbing and natural keys for the truth — both, not either.

-- Surrogate for the plumbing, natural for the truth. Both, not either.
CREATE TABLE customers (                        -- DDL: defines a new table
  id          INTEGER PRIMARY KEY,              -- surrogate: stable, meaningless, narrow
  vat_number  VARCHAR(13) NOT NULL UNIQUE,      -- natural key, ENFORCED rather than assumed
  email       VARCHAR(200) NOT NULL UNIQUE,     -- a second candidate key, also enforced
  name        VARCHAR(200) NOT NULL             -- not a key: two customers may share a name
);                                              -- the semicolon ends the statement

What each declaration costs and buys

INTEGER PRIMARY KEY — in SQLite this exact spelling makes the column the rowid, the physical row address, so lookups by id need no second index. In SQL Server the primary key also becomes the clustered index by default, which decides the physical order of the whole table.
VARCHAR(13) — the 13 is a constraint, not an allocation. Storing 'IT01234567890' costs the characters plus a length, not 13 bytes always. Getting this backwards is what produces tables full of VARCHAR(255).
UNIQUE — implemented as an index, so it also makes lookups by VAT number fast. It permits many NULLs in most engines (two NULLs are not equal), and exactly one in SQL Server. NOT NULL beside it removes the question.

Where NULL came from, and what it costs

🧒 Simple

NULL is not “nothing” and it is not zero. It is a shrug. It means I do not know. And two shrugs are not the same as each other — if I do not know your age and I do not know mine, that does not mean we are the same age.

🎓 Professional

NULL is a marker for unknown or inapplicable, and it turns SQL's boolean logic into three-valued logic: TRUE, FALSE, UNKNOWN. Any comparison with NULL yields UNKNOWN, and WHERE keeps only rows where the predicate is TRUE — so WHERE x = NULL returns nothing, including the rows where x is null. The full damage is chapter 09; what matters here is that every nullable column you declare is a decision to import that logic into your queries.

⚠ Trap

Nullable by default is a design failure, not a convenience. Ask of every column: is there a legitimate business state in which this is unknown? If not, write NOT NULL. Adding it later means cleaning up rows that already violate it, which is a migration, a backfill and an argument — see chapter 36.

Relationships, and the table people forget

One-to-many is a foreign key. Many-to-many is always a third table.

The junction table is where juniors lose marks, because they describe it as “just a link table”. It is not. It is an entity in its own right, and it almost always grows attributes: order_lines needs quantity and the price at the time of sale — which cannot live on products, because that price changes and last year's invoice must not change with it.

✔ Say this in an interview

“A many-to-many needs a junction table, and I would expect it to carry its own columns — on an order line, the quantity and the price as it was when the order was placed, because historical documents must not move when the price list does.” That second clause is the whole answer.

A table is an unordered set. Without ORDER BY there is no order, no matter what you observed in testing.
If position is part of the meaning, it is a column, not an artefact of storage.
A candidate key is minimal and unique; a primary key is the candidate key you nominated, and is also NOT NULL.
Use a surrogate primary key and a UNIQUE constraint on the natural key. Choosing one instead of the other is the mistake.
NULL means unknown, not empty, and it is never equal to anything — including another NULL.
Declare NOT NULL unless there is a real business state where the value is unknown.
One-to-many is a foreign key; many-to-many is always a third table, and that table earns its own columns.

Interview questions this chapter answers

What is the difference between a primary key and a unique constraint?

A primary key is a nominated candidate key: unique and implicitly NOT NULL, one per table, and in most engines it also determines physical row order. A unique constraint enforces the same uniqueness but permits nulls (how many is dialect-specific — SQL Server allows one, PostgreSQL and Oracle allow many) and you can have as many as you need.

Natural key or surrogate key?

Surrogate as the primary key so foreign keys stay narrow and never have to change, plus a unique constraint on the natural key so the business rule is still enforced. The failure mode of surrogate-only is duplicate customers; the failure mode of natural-only is a VAT number correction that has to cascade through six tables.

Why can't NULL = NULL be true?

Because NULL means “unknown”, and two unknowns cannot be shown to be equal. The comparison evaluates to UNKNOWN, and WHERE keeps only TRUE. Use IS NULL, or IS NOT DISTINCT FROM where the dialect has it.