03 — The Relational Model: Keys, Constraints, and NULL

"Just design your tables sensibly" was my relational-model summary, and it skipped the mechanism that makes the design enforceable. The idea that hardened once I wrote it down: constraints are the mechanism by which the database, not the application, becomes the guarantor of correctness. Every primary key, foreign key, and check constraint is a rule the database will refuse to violate, no matter which client is writing [1]. Once I saw integrity as a property of the database rather than something my code has to enforce, schema design stopped being busywork and became the load-bearing layer it actually is.

The vocabulary, pinned down

The relational model has its own terms, and I kept muddling them. Cleaning them up made the rest follow:

• Relation — a table; a set of tuples all conforming to the same heading.
• Tuple — a row; one ordered set of attribute values [2].
• Attribute — a column; a named slot whose values come from a domain (its allowed type and constraints) [3].
• Domain — the set of valid values for an attribute. Postgres lets me define my own with CREATE DOMAIN, layering constraints on top of a base type [4].

A tuple like (1, 'Ave', 'ave@example.com') is just the values of id, name, email for one record. The model is mathematical, but in practice it reads exactly like a row.

Constraints: the rules the database enforces

This is the part I had to take seriously. Constraints are declared once, in the schema, and enforced on every write afterward [1][5]:

• Primary key — uniquely identifies each row; must be unique and non-null. One per table. The anchor everything else keys off.
• Foreign key — a column (or set) that must reference an existing row in another table. Preserves referential integrity: I cannot create an order pointing at a user_id that doesn't exist, and (depending on the rule) I cannot delete a user who still has orders.
• Unique — no two rows share the same value in this column (or column set). Distinct from primary key because a table can have many unique constraints, and they allow NULL.
• Check — an arbitrary boolean condition. CHECK (price >= 0) rejects negative prices at the database layer.
• NOT NULL — the column must have a value. The most underrated constraint; a huge class of bugs disappears the moment I make columns non-nullable by default.
• Exclusion — a generalization of unique: prevents rows whose values overlap under some operator. Classic use is preventing double-booked time ranges.

CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(id),
  total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
  status      TEXT   NOT NULL CHECK (status IN ('pending','paid','refunded')),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (user_id, created_at)
);

That snippet enforces five rules the database will police for the rest of the table's life: id is the anchor, user_id must reference a real user, totals can't be negative, status must be one of three values, and the (user, time) pair is unique. None of that depends on application code behaving correctly. That is the whole point.

NULL is not a value

The relational model treats NULL as "unknown" or "missing" — emphatically not as zero, empty string, or false [6]. The consequence I had to drill in: any arithmetic or comparison involving NULL evaluates to NULL (treated as unknown), not to true or false. NULL = NULL is not true. It's unknown. That's why I cannot use = NULL to test for nullity — I have to use IS NULL.

The trap shows up in aggregates and filters. COUNT(*) counts all rows; COUNT(column) counts non-null values of that column, so they differ. AVG ignores NULLs entirely. A WHERE status != 'paid' excludes rows where status is NULL, because NULL != 'paid' is unknown, not true — so rows with NULL status silently vanish from a "not paid" query. The fix is WHERE status IS DISTINCT FROM 'paid' or explicit status IS NULL OR status != 'paid'.

Postgres gives two helpers for the NULL world [6]:

• COALESCE(a, b, c) — returns the first non-null argument. COALESCE(nickname, 'guest') falls back when nickname is missing.
• NULLIF(a, b) — returns NULL if a = b, else a. Useful for avoiding divide-by-zero: total / NULLIF(count, 0) yields NULL instead of erroring.

Domains: reusable typed constraints

A DOMAIN is a named type-with-constraints I can reuse across columns [4]. Instead of repeating CHECK (email ~ '^[^@]+@[^@]+$') on every table, I define it once:

CREATE DOMAIN email_type AS TEXT
  CHECK (value ~ '^[^@]+@[^@]+\.[^@]+$');

CREATE TABLE users (id BIGSERIAL PRIMARY KEY, email email_type NOT NULL);

Now every column of type email_type inherits the validation. Domains are the relational answer to "don't repeat yourself" — they lift a rule from per-column boilerplate to a named, reusable type.

How I use this

Two habits fall out. First, I make columns NOT NULL by default and only relax it when a value is genuinely optional at write time — most NULL-shaped bugs are columns that should never have been nullable. Second, I push every integrity rule I can into constraints rather than application code: foreign keys, check constraints, unique constraints, and exclusion constraints for range-overlap rules. The application still validates for good UX, but the database is the last line of defense, and a constraint violation is a signal the schema is doing its job. Constraints are not bureaucracy — they are the model.

References

[1] PostgreSQL Global Development Group, "Constraints," 2024. [Online]. Available: https://www.postgresql.org/docs/current/ddl-constraints.html

[2] H. Nasr, "How PostgreSQL Freezes Tuples," Medium, 2023. [Online]. Available: https://medium.com/@hnasr/how-postgres-freezes-tuples-4a9931261fc

[3] Scaler, "Relational Model in DBMS," 2024. [Online]. Available: https://www.scaler.com/topics/dbms/relational-model-in-dbms/

[4] PostgreSQL Global Development Group, "CREATE DOMAIN," 2024. [Online]. Available: https://www.postgresql.org/docs/current/sql-createdomain.html

[5] Tutorialspoint, "PostgreSQL - Constraints," 2024. [Online]. Available: https://www.tutorialspoint.com/postgresql/postgresql_constraints.htm

[6] PostgreSQL Global Development Group, "NULL Values," 2024. [Online]. Available: https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-NULL