Chapter 04 · basic · 6 min
Constraints that encode rules
PRIMARY KEY, UNIQUE and FOREIGN KEY all enforce a relationship between rows or tables. CHECK, DEFAULT and NOT NULL are smaller, but just as load-bearing: they encode a rule about a single value, right in the table definition, so it's true no matter which application, script, or careless INSERT touches the table.
The dataset
One table, inventory, with a NOT NULL name, a CHECK that price must be positive, a CHECK that category is one of three fixed values, and a DEFAULT of 0 on quantity.
Schema
| id | INT |
| name | TEXT |
| price | NUMERIC(10,2) |
| quantity | INT |
| category | TEXT |
Example data
A CHECK constraint is a rule about the value itself
price NUMERIC(10,2) NOT NULL CHECK (price > 0) says more than "price is a number" — it says the number has to be positive. Postgres verifies that expression on every insert, the same way it verifies a foreign key's pointer.
Before you run it — how many columns, and which one do you expect can never legally be negative?
A CHECK violation is refused, the same as a type mismatch
Try to insert a product with a negative price.
Before you run it — -5.00 IS a valid NUMERIC, so the type check alone would pass it. Does the CHECK constraint catch it anyway?
CHECK can restrict to a fixed set of values
category has to be one of 'food', 'household', 'drinks' — a CHECK ... IN (...). This is a cheap, effective alternative to a whole separate lookup table when the set of valid values is small and rarely changes.
Before you run it — 'electronics' isn't food, household, or drinks. Does the insert go through with that category anyway?
DEFAULT fills in a value you didn't provide
quantity has DEFAULT 0. Leave it out of an insert entirely and Postgres fills it in — this is different from NULL, which means "provided, but unknown". RETURNING here shows you the row exactly as it landed, defaults included.
Before you run it — quantity wasn't mentioned in the INSERT at all. Do you expect it to come back as NULL, or as 0?
NOT NULL is the simplest constraint of all
name TEXT NOT NULL has no expression to evaluate — it just refuses a NULL. Try to insert a product with no name.
Before you run it — name has no DEFAULT, so omitting it means NULL. Does that get accepted the way quantity's omission did?
If you've used Excel or Google Sheets
Data Validation with a dropdown list is the closest spreadsheet equivalent to CHECK ... IN (...) — but it only stops typing through the UI. Paste values in, or edit through the API, and validation doesn't run at all. A database CHECK constraint has no such back door: it's enforced by the engine itself, on every write, from every client, always.
These constraints all describe a single table's rules. The next two chapters step back further: whether the columns themselves are organized well, before any constraint gets a chance to enforce anything.