What do primary keys and foreign keys protect in a sample database?

Why

The PostgreSQL documentation defines a primary key as a column or set of columns that must be both unique and not null. A foreign key requires that values in one table match a row in another, which the documentation calls referential integrity. In a lab database, samples has sample_id as its primary key, and results has sample_id as a foreign key referencing samples.

The database then refuses a result for sample S-999 if S-999 was never registered, and refuses to delete a sample that still has results unless the key says what should happen, for example ON DELETE RESTRICT. These rules hold for every program and every person writing to the tables, which is stronger than a check in one script. They also make joins trustworthy, because a many-to-one join on a primary key cannot multiply rows.

Limits matter too. A primary key on an auto-incrementing number does not stop the same physical sample being entered twice with two numbers, so a unique constraint on the real-world identifier is still needed. PostgreSQL does not index the referencing column automatically, so add one if you join or delete often.

And a plain unique constraint allows several rows with NULL unless declared NULLS NOT DISTINCT, available since PostgreSQL 15.