TIL: primary, unique, and foreign keys — the parts I had assumed wrong

Three things about database keys that were fuzzier in my head than I realized:

A primary key is just a unique constraint plus NOT NULL — and a table gets only one. Unique constraints, on the other hand, can exist many per table and they allow NULLs. In PostgreSQL, they even allow multiple rows with NULL in the unique column, because SQL treats NULLs as not equal to each other.

A foreign key doesn't have to point at a primary key. It can reference any column with a unique constraint. The referenced value just has to be guaranteed unique — the PK is only the most common way to get that guarantee.

Foreign key columns are not automatically indexed (in PostgreSQL and SQL Server). The referenced side is indexed by its PK/unique constraint, but the referencing column isn't — so joins and cascading deletes on an unindexed FK column can quietly become table scans:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Worth checking your schema for FK columns nobody ever indexed.