Foreign Keys
Concepts covered: Foreign Keys
A foreign key is a column in one table whose values are drawn from the primary key of another. orders.customer_id holds values that exist in customers.customer_id, and the rule that says so is referential integrity: every value in the child column matches a parent row, or is NULL. In a one-to-many relationship the foreign key sits on the many side: each order has exactly 1 customer, so the customer's id fits in 1 column of the order row.
- customers: customer_id BIGINT PK, email VARCHAR, name VARCHAR, signup_channel VARCHAR
- products: product_id BIGINT PK, sku VARCHAR, product_name VARCHAR
- orders: order_id BIGINT PK, customer_id BIGINT FK, order_date DATE, total DECIMAL
- order_items: order_item_id BIGINT PK, order_id BIGINT FK, product_id BIGINT FK, quantity INT, unit_price DECIMAL
- orders.customer_id references customers.customer_id
- order_items.order_id references orders.order_id
- order_items.product_id references products.product_id
order_items carries 2 foreign keys because a line sits in 1 order and names 1 product. Read from the many side, the diagram is the business: lines belong to orders, orders to customers. A customers.order_id column would instead say each customer has 1 order.
What enforcement buys, and where it is missing
PostgreSQL checks the constraint on every write, as do MySQL and SQL Server: an insert into orders naming a nonexistent customer fails, and deleting a customer with orders fails or cascades according to the declared action. The cloud warehouses behave differently. BigQuery and Snowflake accept FOREIGN KEY declarations, as do Redshift and Databricks, and none of them checks at write time, which is where data engineers get caught. The declaration is metadata the optimizer may use to skip a join it can prove loses no rows, and nothing more.
INSERT INTO orders (order_id, customer_id, order_date, total)
VALUES (90117, 99999999, '2024-04-29', 48.20);Orphans corrupt aggregates without an error
An orphan is a child row whose foreign key matches no parent. FreshCart's deduplication job merged 3,400 duplicate customers like Dorota's second row by deleting the losing row, and 41,000 genuine April orders kept pointing at the deleted ids. An inner join drops those orders, so revenue by segment undercounts by $2.4 million with no error. A left join keeps them with NULL in every customer column, and the $2.4 million lands in a segment the dashboard labels 'Unknown'. Either way finance and sales disagree until someone finds the gap weeks later.
| join type | order rows | revenue reported | where the $2.4M went |
|---|---|---|---|
| inner join | 1,159,000 | $69.0M | dropped from the result |
| left join | 1,200,000 | $71.4M | segment NULL, shown as Unknown |
Delete actions
| action | on parent delete | fits |
|---|---|---|
| RESTRICT / NO ACTION | the delete fails while child rows exist | production OLTP; forces explicit cleanup |
| CASCADE | child rows are deleted too | tightly owned children, such as order_items under orders |
| SET NULL | the child's FK becomes NULL | child is meaningful without the parent, such as orders after a customer erasure |
| SET DEFAULT | the child's FK takes a declared default | rare; needs a real default parent row |
CASCADE from orders to order_items is reasonable, because a line has no meaning without its order. CASCADE from customers to orders deletes revenue history: the deduplication job would have taken 41,000 real orders with it. A merge re-points children to the surviving row before deleting the loser. SET NULL keeps the orders and loses only the link, which is what a GDPR erasure usually wants: the money stays, the person goes. In a warehouse these actions are pipeline logic, and the dimension load checks for referencing facts before it deletes.
FreshCart's nightly load into Snowflake rebuilds dim_product from the merchandising feed, and 40 products discontinued yesterday are absent from today's feed. fact_order_item has 2.1 billion rows, 900,000 of which reference those products. The FOREIGN KEY is declared. What happens tonight?
- Snowflake rejects the rebuild because 900,000 facts still reference the 40 products.
- The 900,000 facts become orphans, and tomorrow's product sales report drops them or shows them under NULL, depending on the join.
- Snowflake sets product_id to NULL on the 900,000 facts.
- The optimizer uses the declared constraint to re-point the facts at current product rows.
The fix keeps discontinued products in the dimension with a status flag, so every fact keeps its parent. In a warehouse that rule lives in the pipeline, because nothing else will apply it.
About this lesson
This section is part of the Keys & Identity lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.