Designing tables: keys, types and constraints

Rules that live with the data

Every problem found so far, the trailing space, the two current mayors, the term that ends before it starts, the shared phone, got into Larkspur County because nothing said it couldn't. Lessons 3 to 5 found them afterwards, with queries. That works, but only for someone who thinks to run the query, and only after the bad data has been read by everything else.

A constraint is a rule stored in the table itself. The database checks it on every insert and every change, from every program, and refuses the row if it breaks the rule. There's no way around it short of changing the table.

Think of a paper form with boxes, versus a blank sheet. On a blank sheet, someone can write a phone number where the date goes, or leave out their name. A form with a box for each answer, some marked "required", some marked "numbers only", catches most of that while it's being filled in, not when someone tries to use it a week later. Constraints are the boxes, and the database is a clerk who won't accept the form until they're right.

Make a scratch database to try them in. In psql:

CREATE DATABASE design6;
\c design6

CREATE DATABASE
You are now connected to database "design6" as user "postgres".

A stricter towns table

Here's Larkspur's towns table again, with rules. The examples in this lesson run in order, in this fresh database, so if you type them in the same order, you'll get the same ids shown here.

CREATE TABLE towns (
    id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        text    NOT NULL CHECK (name = btrim(name) AND name <> ''),
    kind        text    NOT NULL CHECK (kind IN ('city', 'town', 'village')),
    population  integer CHECK (population >= 0),
    founded     integer CHECK (founded BETWEEN 1600 AND 2100),
    website     text    CHECK (website LIKE 'https://%'),
    phone       text    UNIQUE CHECK (phone ~ '^\(\d{3}\) \d{3}-\d{4}$'),
    UNIQUE (name, kind)
);

Each part is explained below, as it refuses something. First, what the database made of it, with \d towns:

                            Table "public.towns"
   Column   |  Type   | Collation | Nullable |           Default            
------------+---------+-----------+----------+------------------------------
 id         | integer |           | not null | generated always as identity
 name       | text    |           | not null | 
 kind       | text    |           | not null | 
 population | integer |           |          | 
 founded    | integer |           |          | 
 website    | text    |           |          | 
 phone      | text    |           |          | 
Indexes:
    "towns_pkey" PRIMARY KEY, btree (id)
    "towns_name_kind_key" UNIQUE CONSTRAINT, btree (name, kind)
    "towns_phone_key" UNIQUE CONSTRAINT, btree (phone)
Check constraints:
    "towns_founded_check" CHECK (founded >= 1600 AND founded <= 2100)
    "towns_kind_check" CHECK (kind = ANY (ARRAY['city'::text, 'town'::text, 'village'::text]))
    "towns_name_check" CHECK (name = btrim(name) AND name <> ''::text)
    "towns_phone_check" CHECK (phone ~ '^\(\d{3}\) \d{3}-\d{4}$'::text)
    "towns_population_check" CHECK (population >= 0)
    "towns_website_check" CHECK (website ~~ 'https://%'::text)

Compare it with Lesson 1's: same columns, and now a list of rules. Postgres named each one (towns_kind_check) and rewrote a few in its own words: BETWEEN became two comparisons, IN became = ANY (ARRAY[...]), and LIKE became ~~. They mean the same.

Generated keys

id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY makes the database hand out the ids itself: 1, 2, 3, in order. The insert doesn't mention id at all, and RETURNING shows what it got:

INSERT INTO towns (name, kind, population, founded, website, phone)
VALUES ('Ashford', 'city', 48210, 1851, 'https://www.ashfordcity.gov', '(555) 010-1100')
RETURNING id, name;

 id |  name   
----+---------
  1 | Ashford
(1 row)

INSERT 0 1

A second Ashford, the village, gets the next one:

INSERT INTO towns (name, kind) VALUES ('Ashford', 'village') RETURNING id, name, kind;

 id |  name   |  kind   
----+---------+---------
  2 | Ashford | village
(1 row)

INSERT 0 1

Two Ashfords are allowed because of UNIQUE (name, kind): unique across the two columns together. Different kinds, no clash. A second Ashford city is another matter:

INSERT INTO towns (name, kind) VALUES ('Ashford', 'city');

ERROR:  duplicate key value violates unique constraint "towns_name_kind_key"
DETAIL:  Key (name, kind)=(Ashford, city) already exists.

ALWAYS means the id has to be the database's choice. Pick your own, and it refuses:

INSERT INTO towns (id, name, kind) VALUES (99, 'Brennan Falls', 'city');

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

That stops two programs choosing the same "next id" at the same time, which is how hand-numbered keys collide. The HINT is the way around it, for loading old data with ids it must keep. Larkspur's own tables set ids by hand, because the practice data needs known ids. Real tables shouldn't.

NOT NULL

NOT NULL means the column must have a value:

INSERT INTO towns (name, kind) VALUES (NULL, 'town');

ERROR:  null value in column "name" of relation "towns" violates not-null constraint
DETAIL:  Failing row contains (4, null, town, null, null, null, null).

DETAIL shows the whole row that was refused. Notice its id: 4, though the last town to get in was 2. Hold that thought.

Only name and kind are NOT NULL here. A town without a known population or website is still a town, and NULL is the honest way to say "unknown". Make a column NOT NULL when a row makes no sense without it.

CHECK

CHECK is a condition every row must meet, written like a WHERE. Each trap from the earlier lessons, refused.

The trailing space, by CHECK (name = btrim(name) AND name <> ''). btrim removes spaces from both ends, so the name must already have none, and can't be empty:

INSERT INTO towns (name, kind) VALUES ('Harlow ', 'town');

ERROR:  new row for relation "towns" violates check constraint "towns_name_check"
DETAIL:  Failing row contains (5, Harlow , town, null, null, null, null).

A kind that isn't one of the three, by CHECK (kind IN ('city', 'town', 'village')):

INSERT INTO towns (name, kind) VALUES ('Harlow', 'County');

ERROR:  new row for relation "towns" violates check constraint "towns_kind_check"
DETAIL:  Failing row contains (6, Harlow, County, null, null, null, null).

A negative population, by CHECK (population >= 0):

INSERT INTO towns (name, kind, population) VALUES ('Harlow', 'town', -5);

ERROR:  new row for relation "towns" violates check constraint "towns_population_check"
DETAIL:  Failing row contains (7, Harlow, town, -5, null, null, null).

And a population that isn't a number at all. No CHECK needed: the column's type is the first rule. An integer column accepts whole numbers, and nothing else:

INSERT INTO towns (name, kind, population) VALUES ('Harlow', 'town', 'about 6000');

ERROR:  invalid input syntax for type integer: "about 6000"
LINE 1: ...name, kind, population) VALUES ('Harlow', 'town', 'about 600...
                                                             ^

A spreadsheet would have kept "about 6000" as text. Choose the type that matches what the value is:

• integer (or bigint for very large ones) for whole numbers you count with.
• numeric for money and anything that must be exact. Never a floating-point type for money: it can't store 0.10 exactly.
• text for text. In Postgres, varchar(50) is no faster; it only adds a length limit.
• date for days, and timestamptz for moments in time, which stores the time zone correctly.
• boolean for yes or no.

Phone numbers and ZIP codes are text, even though they're digits. You never add them up, and a ZIP code like 02134 would lose its leading zero as a number.

Back to CHECK. Cedar Hollow's plain http website, by CHECK (website LIKE 'https://%'):

INSERT INTO towns (name, kind, website) VALUES ('Cedar Hollow', 'town', 'http://cedarhollow.larkspur.us');

ERROR:  new row for relation "towns" violates check constraint "towns_website_check"
DETAIL:  Failing row contains (8, Cedar Hollow, town, null, null, http://cedarhollow.larkspur.us, null).

Fairview's phone format, by a regular expression: ~ means "matches this pattern", and the pattern says a bracket, three digits (\d{3}), a bracket and a space, three digits, a dash, four digits, and nothing else (^ and $ mark the start and end).

INSERT INTO towns (name, kind, phone) VALUES ('Fairview', 'city', '555-010-7700');

ERROR:  new row for relation "towns" violates check constraint "towns_phone_check"
DETAIL:  Failing row contains (9, Fairview, city, null, null, null, 555-010-7700).

A CHECK treats NULL as a pass, not a failure. These rows have no website or phone, and that's allowed. If it shouldn't be, that's what NOT NULL is for.

UNIQUE

UNIQUE means no two rows may share the value. Dunmore, with Ashford's phone number:

INSERT INTO towns (name, kind, phone) VALUES ('Dunmore', 'town', '(555) 010-1100');

ERROR:  duplicate key value violates unique constraint "towns_phone_key"
DETAIL:  Key (phone)=((555) 010-1100) already exists.

But NULLs don't clash. Add two villages with no phone:

INSERT INTO towns (name, kind) VALUES ('Juniper', 'village'), ('Elk Ridge', 'village')
RETURNING id, name, phone;

 id |   name    | phone 
----+-----------+-------
 11 | Juniper   | 
 12 | Elk Ridge | 
(2 rows)

INSERT 0 2

Both accepted. A unique rule compares values, and NULL isn't equal to anything, so any number of rows can have no phone. (The Ashford village has none either.) That's the opposite of Lesson 4, where GROUP BY put all the NULLs in one group, and why the duplicate check had to leave them out first. Here, the database already does.

And look at the ids: 11 and 12. Here's the whole table:

SELECT id, name, kind, phone FROM towns ORDER BY id;

 id |   name    |  kind   |     phone      
----+-----------+---------+----------------
  1 | Ashford   | city    | (555) 010-1100
  2 | Ashford   | village | 
 11 | Juniper   | village | 
 12 | Elk Ridge | village | 
(4 rows)

Four towns, and ids 3 to 10 are missing. Each refused insert had already been given the next id before its rules were checked, and refused rows don't give theirs back. (The made-up 99 and 'about 6000' didn't use one: they failed earlier, while the row was still being read.) Gaps in generated ids are normal. Never use an id to count rows, or to assume nothing was deleted.

Foreign keys, and rules across rows

Now leaders, linked to towns, and one more rule after it, explained in a moment:

CREATE TABLE leaders (
    id       integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    town_id  integer NOT NULL REFERENCES towns (id),
    name     text    NOT NULL CHECK (name = btrim(name) AND name <> ''),
    role     text    NOT NULL CHECK (role IN ('mayor', 'administrator')),
    started  date    NOT NULL,
    ended    date,
    CHECK (ended IS NULL OR ended >= started)
);
CREATE UNIQUE INDEX one_current_mayor ON leaders (town_id)
WHERE role = 'mayor' AND ended IS NULL;

A first leader:

INSERT INTO leaders (town_id, name, role, started)
VALUES (1, 'Thomas Reyes', 'mayor', '2022-01-01') RETURNING id, town_id, name;

 id | town_id |     name     
----+---------+--------------
  1 |       1 | Thomas Reyes
(1 row)

INSERT 0 1

REFERENCES towns (id) is a foreign key: town_id must be an id that exists in towns. A leader of a town that isn't there:

INSERT INTO leaders (town_id, name, role, started) VALUES (99, 'Nobody', 'mayor', '2022-01-01');

ERROR:  insert or update on table "leaders" violates foreign key constraint "leaders_town_id_fkey"
DETAIL:  Key (town_id)=(99) is not present in table "towns".

A CHECK can compare columns in the same row. The last line of the table, CHECK (ended IS NULL OR ended >= started), is Carl Webb's term, refused:

INSERT INTO leaders (town_id, name, role, started, ended)
VALUES (1, 'Carl Webb', 'mayor', '2024-01-01', '2023-06-30');

ERROR:  new row for relation "leaders" violates check constraint "leaders_check"
DETAIL:  Failing row contains (3, 1, Carl Webb, mayor, 2024-01-01, 2023-06-30).

Dunmore's two current mayors need a rule that compares rows, not values in one row: "no two rows with the same town, where the role is mayor and the term hasn't ended". A UNIQUE constraint can't say "where". A partial unique index can, and that's the CREATE UNIQUE INDEX above: unique on town_id, but only among the rows matching its WHERE. A second current mayor for Ashford:

INSERT INTO leaders (town_id, name, role, started) VALUES (1, 'Owen Price', 'mayor', '2023-01-01');

ERROR:  duplicate key value violates unique constraint "one_current_mayor"
DETAIL:  Key (town_id)=(1) already exists.

A past mayor, with an end date, doesn't count. Neither does an administrator:

INSERT INTO leaders (town_id, name, role, started, ended)
VALUES (1, 'Maria Okafor', 'mayor', '2018-01-01', '2022-01-01') RETURNING id, name, ended;

 id |     name     |   ended    
----+--------------+------------
  5 | Maria Okafor | 2022-01-01
(1 row)

INSERT 0 1

INSERT INTO leaders (town_id, name, role, started)
VALUES (1, 'Priya Shah', 'administrator', '2019-06-01') RETURNING id, name, role;

 id |    name    |     role      
----+------------+---------------
  6 | Priya Shah | administrator
(1 row)

INSERT 0 1

(Ids 5 and 6: three refused inserts used 2 to 4.) A date column only takes real dates:

INSERT INTO leaders (town_id, name, role, started) VALUES (1, 'Leo Park', 'administrator', '2026-02-30');

ERROR:  date/time field value out of range: "2026-02-30"
LINE 1: ... started) VALUES (1, 'Leo Park', 'administrator', '2026-02-3...
                                                             ^

And the foreign key works the other way too. Delete a town that still has leaders:

DELETE FROM towns WHERE id = 1;

ERROR:  update or delete on table "towns" violates foreign key constraint "leaders_town_id_fkey" on table "leaders"
DETAIL:  Key (id)=(1) is still referenced from table "leaders".

Refusing is the default, and usually right. You can choose instead: REFERENCES towns (id) ON DELETE CASCADE deletes the town's leaders along with it, and ON DELETE SET NULL keeps them with no town. Cascade sounds tidy and is dangerous: one delete can quietly remove thousands of rows in other tables.

Here's leaders, with every rule, from \d leaders:

                         Table "public.leaders"
 Column  |  Type   | Collation | Nullable |           Default            
---------+---------+-----------+----------+------------------------------
 id      | integer |           | not null | generated always as identity
 town_id | integer |           | not null | 
 name    | text    |           | not null | 
 role    | text    |           | not null | 
 started | date    |           | not null | 
 ended   | date    |           |          | 
Indexes:
    "leaders_pkey" PRIMARY KEY, btree (id)
    "one_current_mayor" UNIQUE, btree (town_id) WHERE role = 'mayor'::text AND ended IS NULL
Check constraints:
    "leaders_check" CHECK (ended IS NULL OR ended >= started)
    "leaders_name_check" CHECK (name = btrim(name) AND name <> ''::text)
    "leaders_role_check" CHECK (role = ANY (ARRAY['mayor'::text, 'administrator'::text]))
Foreign-key constraints:
    "leaders_town_id_fkey" FOREIGN KEY (town_id) REFERENCES towns(id)

Give your rules names, like one_current_mayor. leaders_check tells whoever hits it nothing; term_order would have. To name a CHECK inside CREATE TABLE, write CONSTRAINT term_order CHECK (...).

Adding rules to the real tables

So why not add these rules to Larkspur County? Move back to it, and try:

\c larkspur
ALTER TABLE leaders ADD CONSTRAINT term_order CHECK (ended IS NULL OR ended >= started);

You are now connected to database "larkspur" as user "postgres".
ERROR:  check constraint "term_order" of relation "leaders" is violated by some row

CREATE UNIQUE INDEX one_current_mayor ON leaders (town_id) WHERE role = 'mayor' AND ended IS NULL;

ERROR:  could not create unique index "one_current_mayor"
DETAIL:  Key (town_id)=(5) is duplicated.

ALTER TABLE towns ADD CONSTRAINT one_phone_each UNIQUE (phone);

ERROR:  could not create unique index "one_phone_each"
DETAIL:  Key (phone)=((555) 010-4400) is duplicated.

ALTER TABLE leaders ADD CONSTRAINT no_spaces CHECK (name = btrim(name));

ERROR:  check constraint "no_spaces" of relation "leaders" is violated by some row

A rule can't be added to data that already breaks it. The database checks every existing row first, and refuses if any fails, so a rule in the table is always true of the whole table. The unique ones even name the culprit: town 5, Dunmore, and the shared phone. Nothing was changed: \d leaders still shows no rules but the keys.

So the order is: find the bad rows, fix them, then add the rule, so they can't come back. Lessons 3 to 5 did the finding. Lesson 7 does the fixing, safely. Then the rules go on.

(For CHECK and foreign keys there's a shortcut, ADD CONSTRAINT ... NOT VALID, which checks new and changed rows only, and leaves the old ones for later. Useful on a huge table you can't fix in one go; a trap if "later" never comes.)

Finally, tidy up the scratch database:

\c postgres
DROP DATABASE design6;

You are now connected to database "postgres" as user "postgres".
DROP DATABASE

You can't drop the database you're connected to, hence the \c first.

Spot the problem

Work out what's wrong before you read the explanation.

The rule that made things worse. A team adds CHECK (website LIKE 'https://%'), so every website is secure. A week later, someone adds a small village whose only website is on plain http. The insert is refused, so they leave the website out, and the row goes in with NULL. What went wrong? The rule was stricter than the truth. "This town's website isn't secure" is a real fact worth knowing; "this town has no website" is false. A constraint should only say what is always true. Rules like "should be https" are better as a check that reports, so the fact is kept and the problem is seen. The same goes for the phone pattern above: it would refuse a correct international number.

Where you are now

You can design a table that refuses bad data, and you know why Larkspur County's can't be given the same rules yet. Lesson 7 fixes the bad rows, one careful change at a time, with a way to undo each one. Then the rules go on.

Check yourself

Key terms

• Constraint: a rule stored in a table, checked on every insert and change.
• Identity column: a key the database fills in itself: GENERATED ALWAYS AS IDENTITY.
• NOT NULL: the column must have a value.
• CHECK: a condition every row must meet. NULL passes.
• UNIQUE: no two rows may share the value, or combination of values. NULLs don't clash.
• Foreign key: REFERENCES: the value must exist in another table, and that row can't be deleted while it's referenced.
• Partial unique index: unique only among the rows matching a WHERE.
• Migration: a change to a table's shape, applied in order to every copy of the database.

Go deeper

• The PostgreSQL manual page Constraints (opens in a new tab).
• Try: a CHECK that a meeting's attendance is between 0 and 10,000, on a scratch meetings table. What does it do with a NULL attendance?
• Try: \d on Larkspur's meetings, and list the rules you'd add. Which ones does the current data already break?