Keys and constraints d1

• Browser
• PostgreSQL with PostGIS
• 2 hours

Prerequisites: Window functions (f4)

Decide what makes a row the row it is, and let the database refuse what breaks the rules: primary, alternate and foreign keys, UNIQUE, CHECK and generated columns. The first part runs in this browser, on the tables the Schema page describes; the last part runs in your own PostgreSQL (the setup page).

Concept

An entity is a kind of thing a database keeps facts about, each one with its own identity: an animal, a country, a dataset, a record. Each entity becomes a table, and each table needs a key: a column, or a set of columns, whose value picks out exactly one row.

• A table can have several candidate keys: columns, or sets of columns, that are never NULL and never repeated, so each one picks out every row. species has three: id, slug and scientific_name. The one picked as the main key is the primary key. The others are alternate keys, declared NOT NULL and UNIQUE. species also has six ids from other websites, declared UNIQUE too. Some animals have none of a given kind, so these are optional unique ids, not candidate keys: an id that is missing can't pick out its row.
• A natural key is one the world already uses: a scientific name, a country's ISO code, GBIF's dataset key. A surrogate key is a number the database makes up and that means nothing else: critwire's species.id. Natural keys change (a species moves to another genus, a name turns out to be a synonym), and every row that points at one has to change with it; a surrogate never has to. critwire uses both: id (an integer, never reused) is the primary key, and the natural keys are UNIQUE. A natural key makes a good primary key when an authority keeps it stable and it is never missing: countries.code (ISO 3166-1) and datasets.dataset_key (GBIF's identifier) are primary keys here.
• UNIQUE allows any number of NULLs, because one unknown value isn't equal to another: animals with no NCBI id don't collide. That is why an outside id can be an optional unique id, but not a candidate key, let alone the primary key.
• A foreign key says a column's value must be a key of another table: occurrences.species_id REFERENCES species (id). It checks two things. A value that is inserted or changed must exist in the other table, or be NULL. And a row that other rows still point at can't be deleted or given another key, unless the foreign key says what to do instead (ON DELETE CASCADE deletes the children too, ON DELETE SET NULL empties their column). That is all it checks: not that the value is the right one, not rules that span rows, and with a key of two columns, a row with one of them NULL isn't checked at all (MATCH SIMPLE, the default). PostgreSQL also doesn't index the referencing column for you (Indexes and query plans, d3).
• A CHECK is a condition every row must meet, such as month BETWEEN 1 AND 12. It refuses a row only when the condition is false. When it is NULL (unknown, Select and filter, f1) the row passes, so a CHECK doesn't replace NOT NULL. A CHECK can compare columns of the same row: taxa's says a class has no parent and every other rank has one.
• A generated column is computed from other columns of its row and can't be written directly. The lab's occurrences.geom is the point made from longitude and latitude, so the point and the two numbers can never disagree.
• Foreign keys set the order of a load: a parent row goes in before its children (the browser setup inserts taxa one rank at a time, top down), and out after them.

Worked example

Can two animals share an id? The standard view information_schema.table_constraints lists a table's constraints. DuckDB also lists each NOT NULL rule, as a CHECK; this query leaves those out:

Try to add a second animal with the tiger's id, 25. INSERT INTO adds a row: the columns in brackets, then their values in the same order. The database refuses, and nothing is added:

A new id doesn't get a row past the other unique columns either. A Bengal tiger with its own id but given the tiger's GBIF id is refused by UNIQUE (gbif_id). That one GBIF id names one animal is true of critwire's animals, and critwire's build checks it (two animals sharing an outside id fail it); it isn't a rule of GBIF's. A project that lists subspecies could have two animals under one GBIF taxon, and then the column couldn't be UNIQUE.

Why not use an outside id as the primary key? Some are missing, and UNIQUE lets the NULLs sit side by side:

And the scientific name? Names change. These four animals' GBIF ids are synonyms in GBIF's backbone: GBIF files their records under another, accepted name (the lab data page).

Had scientific_name been the primary key, following such a change would mean rewriting it in species, species_sources, species_countries and every record. With id as the key it is one UPDATE of one row, and every foreign key still points at the right animal.

Lab

1. What does a CHECK refuse? A record with month 13 (a copy of the first devil record, with a new id and the month changed):

   Query d1-check-month INSERT INTO occurrences (id, species_id, dataset_key, license, basis_of_record, longitude, latitude, year, month) SELECT 1, species_id, dataset_key, license, basis_of_record, longitude, latitude, year, 13 FROM occurrences WHERE species_id = 48 ORDER BY id LIMIT 1;

   RunStopReset databaseSchema

2. The same record with no month and no country goes in. month BETWEEN 1 AND 12 is unknown for NULL, and a CHECK passes unknown; a foreign key passes NULL too. Most of the time that is what you want (many records really have no month), but if a value must be there, the column needs NOT NULL as well.

   Query d1-check-null INSERT INTO occurrences (id, species_id, dataset_key, license, basis_of_record, longitude, latitude, country_code, year, month) SELECT 1, species_id, dataset_key, license, basis_of_record, longitude, latitude, NULL, year, NULL FROM occurrences WHERE species_id = 48 ORDER BY id LIMIT 1; SELECT id, species_id, country_code, month FROM occurrences WHERE id = 1;

   RunStopReset databaseSchema

3. A CHECK across two columns: a genus must have a parent.

   Query d1-check-taxa INSERT INTO taxa (name, taxon_rank, parent_name) VALUES ('Dracula', 'genus', NULL);

   RunStopReset databaseSchema

4. What does a foreign key refuse? An animal in a genus critwire doesn't have: species.genus must be a name in taxa. Only a name: the foreign key would take a family's name as well (step 7).

   Query d1-fk-genus INSERT INTO species (id, slug, scientific_name, common_name, genus, range_status) VALUES (1000, 'dracula-ant', 'Dracula ant', 'Dracula Ant', 'Dracula', 'ok');

   RunStopReset databaseSchema

   The rest of the lab runs in PostgreSQL (the setup page), on the same tables.

5. A foreign key also protects the parent. Deleting the dataset with the most records in the sample would leave its records pointing at nothing, so PostgreSQL refuses:

   Checked 2026-11-02 on PostgreSQL 16.15, PostGIS 3.4.2, GEOS 3.12.1, GDAL 3.8.4.

   d1-fk-delete · PostgreSQL with PostGIS Copy Lab schema

   DELETE FROM datasets
   WHERE dataset_key = (SELECT dataset_key FROM occurrences
                        GROUP BY dataset_key
                        ORDER BY count(*) DESC, dataset_key
                        LIMIT 1);

   -- result: d1-fk-delete · error: statement 1 of 1: update or delete on table "datasets" violates foreign key constraint "occurrences_dataset_key_fkey" on table "occurrences"

   In d1.sql, with the blocks it runs after.

6. What doesn't a foreign key check? Whether the value is the right one. This copy of a devil record says Australia (AU is a countries row, so the foreign key is satisfied) but sits at Brasília. The point is the generated column, made from the longitude and latitude just inserted, so a spatial test can catch what the key can't (Spatial joins, g3, does this for every record):

   d1-fk-wrong · PostgreSQL with PostGIS Copy Lab schema

   INSERT INTO occurrences (id, species_id, dataset_key, license, basis_of_record, longitude, latitude,
                            country_code_gbif, country_code, year, month)
   SELECT 1, species_id, dataset_key, license, basis_of_record, -47.9, -15.8, 'AU', 'AU', year, month
   FROM occurrences
   WHERE species_id = 48
   ORDER BY id
   LIMIT 1;
   SELECT o.id, o.country_code, ST_AsText(o.geom) AS point,
          ST_Covers(cs.geom, o.geom) AS in_its_country,
          (SELECT s.country_code FROM country_shapes AS s WHERE ST_Covers(s.geom, o.geom)) AS point_is_in
   FROM occurrences AS o
   JOIN country_shapes AS cs ON cs.country_code = o.country_code
   WHERE o.id = 1;

   -- result: d1-fk-wrong · 1 row · sha256 18d790a2949e
   id | country_code | point | in_its_country | point_is_in
   1 | AU | POINT(-47.9 -15.8) | false | BR

   In d1.sql, with the blocks it runs after.

7. Nor does species.genus REFERENCES taxa (name) check that the name is a genus: Felidae, a family, would pass. A CHECK can't help, because it sees only its own row. A foreign key over two columns can. Give taxa a UNIQUE (name, taxon_rank) to point at, give species a rank column that can only say genus, and point the pair at the pair. Both columns are NOT NULL, so the key checks every row (MATCH SIMPLE skips a pair with a NULL in it). Adding the key checks the rows already there, and all 76 animals pass:

   d1-rank-fk · PostgreSQL with PostGIS Copy Lab schema

   ALTER TABLE taxa ADD CONSTRAINT taxa_name_rank_key UNIQUE (name, taxon_rank);
   ALTER TABLE species
     ADD COLUMN genus_rank text NOT NULL DEFAULT 'genus' CHECK (genus_rank = 'genus'),
     ADD CONSTRAINT species_genus_rank_fkey FOREIGN KEY (genus, genus_rank) REFERENCES taxa (name, taxon_rank);
   SELECT genus_rank, count(*) AS species
   FROM species
   GROUP BY genus_rank;

   -- result: d1-rank-fk · 1 row · sha256 d6883c437319
   genus_rank | species
   genus | 76

   In d1.sql, with the blocks it runs after.

   Then moving the tiger into the family Felidae is refused:

   d1-rank-wrong · PostgreSQL with PostGIS Copy Lab schema

   Runs after: d1-rank-fk

   UPDATE species SET genus = 'Felidae' WHERE id = 25;

   -- result: d1-rank-wrong · error: statement 1 of 1: insert or update on table "species" violates foreign key constraint "species_genus_rank_fkey"

   In d1.sql, with the blocks it runs after.

   taxa's own parent_name can be held to the rank one up the same way: a parent_rank column whose CHECK ties it to the row's rank, and a foreign key from (parent_name, parent_rank). No trigger is needed. The lab tables don't carry these keys; the lab file checks every rank and parent as it loads (the setup page).

8. PostgreSQL builds an index for every primary key and UNIQUE constraint, because it needs one to check them. It doesn't for a foreign key: checking a new record needs only the parent's key, which is indexed already. But deleting a parent, or joining from it, has to find the children, and with no index on occurrences.species_id that means reading the whole table (Indexes and query plans, d3). PostgreSQL describes itself in its catalog, tables such as pg_constraint, pg_attribute (every column) and pg_index; 'occurrences'::regclass turns a table's name into the id they use, and an index's column list counts from 0, a constraint's from 1. Which of occurrences' keys have an index that starts with their column?

   d1-fk-index · PostgreSQL with PostGIS Copy Lab schema

   SELECT c.conname AS constraint_name,
          CASE c.contype WHEN 'p' THEN 'PRIMARY KEY' ELSE 'FOREIGN KEY' END AS constraint_type,
          a.attname AS column_name,
          EXISTS (SELECT 1 FROM pg_index AS i
                  WHERE i.indrelid = c.conrelid AND i.indkey[0] = c.conkey[1]) AS indexed
   FROM pg_constraint AS c
   JOIN pg_attribute AS a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
   WHERE c.conrelid = 'occurrences'::regclass
     AND c.contype IN ('p', 'f')
   ORDER BY c.contype DESC, c.conname;

   -- result: d1-fk-index · 4 rows · sha256 bbc2c2e237f8
   constraint_name | constraint_type | column_name | indexed
   occurrences_pkey | PRIMARY KEY | id | true
   occurrences_country_code_fkey | FOREIGN KEY | country_code | false
   occurrences_dataset_key_fkey | FOREIGN KEY | dataset_key | false
   occurrences_species_id_fkey | FOREIGN KEY | species_id | false

   In d1.sql, with the blocks it runs after.

9. The generated column, as the catalog describes it; then an attempt to write it, refused:

   d1-generated · PostgreSQL with PostGIS Copy Lab schema

   SELECT column_name, is_generated, generation_expression
   FROM information_schema.columns
   WHERE table_schema = current_schema()
     AND table_name = 'occurrences'
     AND is_generated = 'ALWAYS';

   -- result: d1-generated · 1 row · sha256 5927c3d4ced9
   column_name | is_generated | generation_expression
   geom | ALWAYS | st_setsrid(st_makepoint(longitude, latitude), 4326)

   In d1.sql, with the blocks it runs after.

   d1-generated-write · PostgreSQL with PostGIS Copy Lab schema

   UPDATE occurrences
   SET geom = ST_SetSRID(ST_MakePoint(0, 0), 4326)
   WHERE id = (SELECT min(id) FROM occurrences);

   -- result: d1-generated-write · error: statement 1 of 1: column "geom" can only be updated to DEFAULT

   In d1.sql, with the blocks it runs after.

10. A surrogate key made by the database. GENERATED ALWAYS AS IDENTITY numbers the rows as they go in. A surrogate key alone would accept the same note twice under two numbers, so the natural key, (animal, observer, day), is declared UNIQUE beside it:

    d1-identity · PostgreSQL with PostGIS Copy Lab schema

    CREATE TABLE field_notes (
      note_id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      species_id integer NOT NULL REFERENCES species (id),
      observer   text NOT NULL CHECK (observer <> ''),
      seen_on    date NOT NULL,
      UNIQUE (species_id, observer, seen_on)
    );
    INSERT INTO field_notes (species_id, observer, seen_on)
    VALUES (48, 'Ana', DATE '2026-03-14'), (48, 'Ana', DATE '2026-03-15'), (25, 'Ben', DATE '2026-03-14');
    SELECT note_id, species_id, observer, seen_on
    FROM field_notes
    ORDER BY note_id;

    -- result: d1-identity · 3 rows · sha256 f171f9ff7119
    note_id | species_id | observer | seen_on
    1 | 48 | Ana | 2026-03-14
    2 | 48 | Ana | 2026-03-15
    3 | 25 | Ben | 2026-03-14

    In d1.sql, with the blocks it runs after.

    d1-identity-twice · PostgreSQL with PostGIS Copy Lab schema

    Runs after: d1-identity

    INSERT INTO field_notes (species_id, observer, seen_on)
    VALUES (48, 'Ana', DATE '2026-03-14');

    -- result: d1-identity-twice · error: statement 1 of 1: duplicate key value violates unique constraint "field_notes_species_id_observer_seen_on_key"

    In d1.sql, with the blocks it runs after.

Result

Every key refused what it should: a second animal with id 25 or with the tiger's GBIF id, a month of 13, a genus with no parent, an animal in a genus that doesn't exist, a genus that is a family once a two-column foreign key checked the rank, the deletion of a dataset its records still point at, a write to a generated column and the same field note twice. What got through is as instructive: a record with no month and no country (a CHECK and a foreign key both pass NULL), and a record that says Australia while its point is in Brazil (a foreign key checks that a value exists, not that it is true). occurrences' primary key is indexed and its three foreign keys aren't. critwire's design follows from this: a surrogate primary key, the natural keys NOT NULL and UNIQUE, the outside ids UNIQUE but optional (some are missing, and four names already changed in GBIF's eyes), and natural primary keys only where an authority keeps them stable (countries, datasets).

Exercise

critwire keeps one photo per animal, in species. Design a table species_photos that can hold several: a surrogate photo_id; the animal (species_id, an animal critwire has); a url no other photo has; a license that is a Creative Commons license (it starts with CC) or Public domain; and a credit that is filled in and not empty. Create it in the browser, copy in every animal's current photo (photo_url, photo_license, photo_credit, numbered by species id), and count the photos, the animals and the licenses.

CREATE TABLE species_photos (
  photo_id   INTEGER PRIMARY KEY,
  species_id INTEGER NOT NULL REFERENCES species (id),
  url        VARCHAR NOT NULL UNIQUE,
  license    VARCHAR NOT NULL CHECK (license LIKE 'CC%' OR license = 'Public domain'),
  credit     VARCHAR NOT NULL CHECK (trim(credit) <> '')
);
INSERT INTO species_photos
SELECT row_number() OVER (ORDER BY id), id, photo_url, photo_license, photo_credit
FROM species
WHERE photo_url IS NOT NULL;
SELECT count(*) AS photos, count(DISTINCT species_id) AS species, count(DISTINCT license) AS licenses
FROM species_photos;

Then each of these must be refused by your table: the axolotl's photo again, a photo of an animal critwire doesn't have, and a photo with no license to reuse.

Check

Your counts must equal the answer's, in any row order. Then the page runs the three inserts above after your answer, and each must fail with the error named (unique, foreign key, CHECK), whatever you named your constraints. Check runs on the tables as loaded, so if you changed them in the playground, Reset database first.

Second exercise, on your own computer

The page doesn't check this one. Read the lab's ERD back out of the database. Reverse engineering a design means recovering it from what was built: tbls (MIT license, a single program file) reads PostgreSQL's catalog (every table, column and foreign key) and writes a Mermaid ERD. Get version 1.96.1 from its release page, check the file's SHA-256 against the one printed here (the release lists the other systems'), and point it at your lab database. It finds the password in the pgpass.conf the setup made. In PowerShell:

curl.exe -fLO https://github.com/k1LoW/tbls/releases/download/v1.96.1/tbls_v1.96.1_windows_amd64.zip
(Get-FileHash tbls_v1.96.1_windows_amd64.zip -Algorithm SHA256).Hash -eq "ecd641e0d1ffde95350b1336cb03603c7f7fd8c7fc20a996323ea80d8594a5c6"   # must print True
Expand-Archive tbls_v1.96.1_windows_amd64.zip -DestinationPath tbls
.\tbls\tbls.exe out -t mermaid --include "learn_curriculum.*" -o lab-tbls.mmd "postgres://postgres@localhost:5432/learn?sslmode=disable"

On Linux (x86-64), with the password in ~/.pgpass:

curl -fLO https://github.com/k1LoW/tbls/releases/download/v1.96.1/tbls_v1.96.1_linux_amd64.tar.gz
echo "18285eef0ba7d917214e8bd7dd6a22afe5fea021eed52e7149100a3d10a2af95  tbls_v1.96.1_linux_amd64.tar.gz" | sha256sum -c   # must print OK
tar xzf tbls_v1.96.1_linux_amd64.tar.gz tbls
./tbls out -t mermaid --include 'learn_curriculum.*' -o lab-tbls.mmd "postgres://postgres@localhost:5432/learn?sslmode=disable"

Open lab-tbls.mmd beside the lab schema's diagram. What do the two agree on, and what does each have that the other doesn't?

Dialects

• PostgreSQL: as here, with surrogate keys from GENERATED ALWAYS AS IDENTITY (or the older serial); generated columns are STORED only (virtual ones come in PostgreSQL 18); foreign keys take ON DELETE CASCADE | SET NULL | RESTRICT, can be DEFERRABLE (checked at commit), and can be added NOT VALID and checked later with VALIDATE CONSTRAINT; UNIQUE NULLS NOT DISTINCT makes NULLs collide; no automatic index on a foreign key.
• DuckDB (this page's browser engine): keys as here, but foreign keys only in CREATE TABLE, with no CASCADE or SET NULL actions; no identity columns (a CREATE SEQUENCE and DEFAULT nextval('…') do it); generated columns are virtual only; primary keys and UNIQUE build an index, which also makes bulk loads into keyed tables slower.
• SQL Server: IDENTITY(1, 1) for surrogates; computed columns (AS (…), PERSISTED to store them); a UNIQUE constraint allows only one NULL (a filtered unique index, WHERE col IS NOT NULL, allows many); foreign keys take ON DELETE CASCADE | SET NULL, and NOCHECK turns one off, which leaves it untrusted until it is re-checked; CHECK passes unknown, as here.

Schema: the browser's tables

Every column, in words · The diagram alone

Close

Lab schema: the PostGIS tables

Every column, in words · The diagram alone

Close

Mark done