Foreign Keys

Concepts: dmForeignKeys

A foreign key is a column in one table that references the primary key of another. It is how relational databases enforce referential integrity: every FK value must either match an existing PK in the parent table, or be NULL. Without FK enforcement, your tables can accumulate orphaned rows that reference customers, products, or orders that no longer exist. Cloud Data Warehouses Do Not Enforce FKs This is one of the most important things to know as a data engineer. Redshift, BigQuery, Snowflake, and Databricks all let you declare FK constraints but do not enforce them at write time. You can insert an order with customer_id = 999 even if no customer 999 exists. The constraint is purely informational, used by the query optimizer for join planning. Cascade Behavior The FK Design Principle Thre