products.category_id holds ids of categories. The products tab shows a link icon next to it. What stops a product with category_id 99, when no category 99 exists? A foreign key.

REFERENCES

CREATE TABLE products (
  id          integer PRIMARY KEY,
  name        text NOT NULL,
  category_id integer REFERENCES categories (id)
);

A foreign keyforeign keyA column whose values must exist in a key column of another table is a column whose values must exist in a key column of another table.

• products is the referencing table (the child). categories is the referenced table (the parent).
• The referenced column must be a primary key or UNIQUE.
• NULL is accepted, unless you add NOT NULL. A product with NULL has no category.

This rule is referential integrity: every non-NULL value of a foreign key points to a row that exists.

The database refuses a link to nothing

ERROR: insert or update on table "products" violates foreign key constraint "products_category_id_fkey"
DETAIL: Key (category_id)=(6) is not present in table "categories".

Add the parent row first, then the child row.

The parent is protected

A table that another table references cannot be dropped:

ERROR: cannot drop table categories because other objects depend on it
DETAIL: constraint products_category_id_fkey on table products depends on table categories

Removing one referenced row is refused too. The next lesson shows how, and how to choose what happens to the child rows.

The other form

FOREIGN KEY after the columns does the same thing:

CREATE TABLE products (
  id          integer PRIMARY KEY,
  name        text NOT NULL,
  category_id integer,
  FOREIGN KEY (category_id) REFERENCES categories (id)
);