What is a foreign key?

A foreign key is a column in one table that holds the primary key of a row in another table. It’s the wire that connects two tables: orders.customer_id holds a value that exists over in customers.id, so every order knows which customer it belongs to.

A concrete example

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT REFERENCES customers (id),
  total NUMERIC
);

The REFERENCES clause is what makes customer_id a real foreign key rather than an integer column with a suggestive name. Once it’s declared, the database enforces referential integrity: you can’t insert an order pointing at customer 9999 if no such customer exists, and depending on how the constraint is configured, you can’t delete a customer who still has orders — or the delete cascades to those orders.

Cardinality: the one-to-many pattern

Foreign keys are how you express relationships in a schema:

• One-to-many: one customer has many orders. The foreign key lives on the “many” side — orders carries customer_id, not the other way round.
• Many-to-many: students enroll in many courses, and courses hold many students. This needs a third junction table carrying two foreign keys, one to each side.
• One-to-one: less common, usually used to split rarely-read or sensitive columns into a separate table.

On an entity relationship diagram, foreign keys are the lines between boxes, and the notation at the ends of each line tells you which cardinality applies.

Foreign keys and joins

A foreign key is the natural join condition between two tables. The relationship is already declared, so the query practically writes itself:

SELECT
  customers.name,
  SUM(orders.total) AS lifetime_value
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
GROUP BY customers.name

Note that a foreign key column is not unique — that’s the whole point. Many orders can carry the same customer_id, which is why joining from the customer side to the order side multiplies rows, and why you usually follow the join with an aggregation.

Missing constraints in the warehouse

Foreign key constraints are standard in transactional databases, where they protect application data. They’re often absent in analytical databases, where data arrives through pipelines and constraint checking would only slow loads down. The relationship still exists conceptually — the column is a foreign key in every sense that matters to an analyst — it just isn’t enforced, so nothing stops an orphaned customer_id from showing up in your reports.

Foreign keys in Metabase

Metabase reads declared foreign keys when it syncs a database and tags those columns with the Foreign Key field type, which is what lets Metabase link related tables and offer drill-through from a value to the record it points at. When a warehouse table doesn’t declare its constraints, an admin can assign the Foreign Key field type and its target column by hand on the Data Model page.

Related terms

• Entity key
• Primary key
• Join
• ERD

Further reading

• Database table relationships
• Combining tables with joins

Put it to work

• Salesforce — Integration
• Sales analytics — Overview
• Lead funnel dashboard — Dashboard