A primary key uniquely identifies each row in a table; a foreign key references the primary key of another table, establishing a relationship between tables and enforcing referential integrity (you can't reference a row that doesn't exist).
users (
id ,
name (),
email ()
);
A primary key is unique and not null — it identifies each row. It's automatically indexed (fast lookups) and is what foreign keys reference.
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
total DECIMAL(10,2),
FOREIGN KEY (user_id) REFERENCES users(id) -- user_id must match a users.id
);
A foreign key (user_id) references another table's primary key (users.id), creating the relationship "an order belongs to a user." The database enforces this: you can't insert an order with a user_id that doesn't exist in users.
INSERT INTO orders (user_id, total) VALUES (999, 50); -- ❌ ERROR if user 999 doesn't exist
-- the foreign key PREVENTS orphaned records (orders referencing non-existent users)
Referential integrity means the database guarantees relationships are valid — you can't have an order referencing a non-existent user. This prevents data corruption from orphaned/invalid references.
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE -- delete the user → delete their orders too
-- ON DELETE SET NULL -- set user_id to NULL
-- ON DELETE RESTRICT -- prevent deleting a user who has orders (default-ish)
ON DELETE/ON UPDATE define what happens to dependent rows when the referenced row is deleted or changed — CASCADE (propagate), SET NULL, or RESTRICT (prevent). Choosing correctly is important to avoid accidental data loss or orphaned rows.
Primary keys and foreign keys are fundamental to relational database design — they're how the relational model establishes identity and relationships between tables, so understanding them is essential for designing and working with any relational database. Primary keys (uniquely identifying each row, auto-indexed) are the foundation of table identity and what relationships reference. Foreign keys establish the relationships between tables (orders belong to users, etc.) that define the relational structure of an application's data.
Critically, foreign keys enforce referential integrity — the database guarantees relationships are valid (you can't reference a non-existent row), which prevents data corruption from orphaned or invalid references, a major reliability benefit that maintains data consistency automatically.
Understanding the ON DELETE/ON UPDATE behaviors (CASCADE, SET NULL, RESTRICT) is important because they determine what happens to related data when a referenced row changes or is deleted — choosing correctly prevents accidental data loss (e.g. CASCADE deleting more than intended) or orphaned records, a consequential design decision.
Since relationships between tables are the essence of relational databases, and since primary/foreign keys (and referential integrity) are how those relationships are defined and protected, understanding them — keys for identity and relationships, referential integrity for data consistency, and the on-delete behaviors for managing related data — is core, foundational knowledge for database design and a common interview topic essential to working correctly with relational data.