DataslopeForeign Keys
Learn how foreign keys encode relationships between tables by pointing stable references at primary keys.
An order belongs to a customer. A comment belongs to a post. A payment belongs to an invoice. In a database design, those sentences must become columns and constraints.
A foreign key is the design tool that turns "belongs to" into a trustworthy relationship.
A relationship needs a reference
A foreign key is a column in one table that stores the primary key value of a related row in another table.
The orders.customer_id column points at customers.customer_id. One
customer can have many orders, and each order belongs to one customer.
That arrow is more than a drawing. When you declare the constraint, PostgreSQL checks that the referenced customer actually exists.
Why store an id instead of duplicating data?
A tempting design is to copy the customer's name and email onto every order. That feels simple until the data changes.
A foreign key keeps the customer's facts in one place and stores only the stable identity on related rows.
This is a design for consistency. The order does not need its own copy of the customer's email; it needs a reliable reference to the customer.
Quick check
In a well-designed orders table, why store customer_id instead of customer_email and customer_name on every order?
Because names and emails cannot be queried with SQL.
Because the customer row stays the single source of truth while orders keep a stable reference.
Because every order must have a different customer.
Because PostgreSQL refuses to store text in an orders table.
Foreign keys enforce the relationship
A foreign key is not just documentation. It is a rule the database
defends. If an order points to customer 999 and no such customer row
exists, PostgreSQL rejects the order.
The rejected insert prevents an orphan row: a child row whose parent row does not exist.
Parent and child tables
When discussing foreign keys, people often say parent table and child table.
- The parent table is the table being referenced.
- The child table is the table holding the foreign key.
The child points to the parent because the child row depends on the parent's identity. An order cannot meaningfully belong to a customer who does not exist.
Quick check
What does a foreign key constraint add beyond simply naming a column customer_id?
It makes the column display with a special color in SQL results.
It causes all customers to have exactly one order.
It makes PostgreSQL verify that each stored id points to an existing parent row.
It copies the customer's name into the order automatically.
Foreign keys describe cardinality
Foreign keys often encode cardinality, the shape of a relationship. In the common one-to-many case, many child rows can reference one parent row.
The foreign key lives on the many side: each book stores the author it
belongs to. If a book could have many authors too, the design would need
a separate junction table, not just one author_id column.
Check your understanding
What is a foreign key?
A column that stores a random value unrelated to other tables.
A column or columns that reference a primary key or unique key in another table.
A required copy of every column from the parent table.
A second table name written inside a query.
In a customer-order design, which table is usually the child table?
customers, because customers have names.
orders, because each order stores customer_id and depends on a customer.
Both tables, because every table must have the same columns.
Neither table, because parent and child only apply to file systems.
What happens when a foreign key blocks customer_id = 99 in orders?
PostgreSQL creates customer 99 automatically.
PostgreSQL changes 99 to null silently.
The insert is rejected because no referenced customer row exists.
The whole database is deleted.
Where does the foreign key usually live in a one-to-many relationship?
On the one side, because it has fewer rows.
On the many side, because each child row points to the one parent row it belongs to.
In a separate database that stores only keys.
In every column of both tables.