Foreign keys

A foreign key is a column in one table (the child) whose values must match the key of another table (the parent). In the starter data, orders.customer_id references customers.customer_id: each order belongs to a customer who exists. You declare it with REFERENCES:

customer_id integer NOT NULL REFERENCES customers (customer_id)

If you leave out the column list, REFERENCES customers points at the parent's primary key, which is almost always what you want.

Once the foreign key exists, PostgreSQL checks it on every change, from both sides:

• Inserting or updating a child with a value that has no parent fails: insert or update on table "orders" violates foreign key constraint. NULL is allowed unless the column is also NOT NULL (a NULL reference means "no parent").
• Deleting a parent that still has children, or changing its key, is handled by the foreign key's ON DELETE (and ON UPDATE) action.

The referenced column must be a primary key or have a UNIQUE constraint. That's logical: a reference must point at exactly one row. Referencing products (product_name) fails with there is no unique constraint matching given keys for referenced table.