What is a Foreign Key?

A Foreign Key (FK) is a column or set of columns in a database table that refers to the Primary Key (PK) of another table. Its primary purpose is to establish and enforce a link between two tables, ensuring referential integrity.

This link prevents "orphan" records by ensuring that a value in the FK column must exist as a PK in the referenced "parent" table. For instance, an order record cannot exist for a customer_id that doesn't correspond to an actual customer. Databases can enforce actions when a referenced PK is deleted or updated, such as ON DELETE CASCADE (deletes dependent rows), ON DELETE SET NULL (sets FK to NULL), or ON DELETE RESTRICT (prevents deletion of the parent PK).

Consider an orders table with a customer_id column referencing the id (PK) in a customers table.

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
);

While crucial for transactional OLTP systems, enforcing FK constraints in analytical data warehouses (OLAP) like BigQuery or Snowflake is often relaxed or handled differently. The overhead of checking every insert/update can significantly impact ETL/ELT performance, especially with large-scale batch loads. In these environments, referential integrity is frequently managed upstream in the data pipeline (e.g., during data ingestion, transformation using tools like dbt, or validated with data quality checks) rather than at the database level, treating FKs as metadata hints rather than strict enforcement.

In the interview, also mention that while traditional RDBMS enforce FKs, modern distributed data systems often treat them as metadata for query optimization or rely on ETL processes for data integrity.