Foreign keys: the row that points at nobody
Step 1 of 110%
Concept · step 1 of 11
What a foreign key is
A foreign key constraint says that the values in a column, or a group of columns, must match the values in some row of another table. Postgres calls what it protects referential integrity. The board shows one, drawn as the line between the two tables.
- The referencing column.
orders.customer_id, the one holding the value. - The referenced column.
customers.id, which must be a primary key, a unique constraint or a non-partial unique index, so each value points at exactly one row. - Checked on every write that could break it. inserting or updating an order, and deleting a customer or changing its id. A NULL in the referencing column skips the check.
customer_id BIGINT NOT NULL REFERENCES customers (id)Why in the database and not in your code? Because your code is not the only writer: a second service, a migration, an import script, two requests racing each other. The database sees every write; your application only sees its own. The next steps show what happens without the rule, and what the rule really does.
PostgreSQL docs: Foreign Keys ↗Starting Postgres in your browser
About 5 MB the first time, cached after that.
PostgreSQL running in your browser