What are primary keys and foreign keys? Why are they important?

Primary keys (PKs) uniquely identify each record within a table, ensuring entity integrity, while foreign keys (FKs) establish and enforce relationships between tables by referencing primary keys, maintaining referential integrity.

Mechanics and Importance

Primary Keys are a column or set of columns that uniquely identify each row in a table. They must be UNIQUE and NOT NULL. This absolute uniqueness is critical for data integrity, preventing duplicate records (e.g., two distinct customers sharing the same ID). PKs are often implemented with a clustered index, which physically orders the data rows on disk according to the key. This significantly improves read performance for specific lookups and range queries, reducing disk I/O, and profoundly optimizes join operations by co-locating related data. PKs are also fundamental for change data capture (CDC) and data replication, as they provide a stable, immutable identifier for tracking row-level changes across systems.

Foreign Keys are columns in one table that refer to the primary key in another table, establishing a parent-child relationship. They enforce referential integrity, meaning you cannot create "orphan" child records without a valid parent, nor delete a parent record if dependent child records exist (unless cascading actions like ON DELETE CASCADE are explicitly defined). This ensures data consistency across related tables, accurately models real-world relationships, and prevents erroneous data states.

Trade-offs and Real-world Applications

While essential for data integrity, enforcing PK and FK constraints adds overhead. In OLTP systems, enforced constraints are common to guarantee transactional consistency and prevent data corruption. However, in data warehouses or data lakes (e.g., Snowflake, Delta Lake), FKs are often logical (declared but not enforced by the database engine) to prioritize ingestion performance. Enforcing constraints during large-scale ETL/ELT operations would incur significant validation costs on INSERT/UPDATE statements, slowing down pipelines. Instead, data integrity is managed upstream through robust ETL/ELT logic, data quality checks, and tools like dbt for schema validation and testing. PKs, however, remain crucial for efficient joins and data partitioning strategies in analytical systems, directly impacting query performance (e.g., optimizing Spark shuffle operations or Snowflake micro-partition pruning).

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

In the interview, also mention how these concepts guide data modeling, impact query optimizers, and are crucial for data lineage and governance.