What is the difference between a primary key and a foreign key?
Assesses fundamental understanding of DBMS conventions, runtime behavior, and memory/performance considerations.
Hiring managers look for precision, avoidance of ambiguous jargon, and ability to explain trade-offs under real production conditions.
A primary key uniquely identifies each row in a table. It must be unique and not null, and a table has at most one. It may be a single column or a composite of several, and it is usually backed by a unique index. A natural key comes from the data, such as an email or ISBN; a surrogate key is generated, such as an auto-increment id or UUID.
A foreign key is a column, or set of columns, in one table that references the primary key or unique key of another. It enforces referential integrity: a child row cannot reference a parent that does not exist.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id)
);
Deletes and updates on the parent can be restricted, cascaded or set null depending on the rule. Index the foreign key column, because otherwise joins and cascade checks scan the child table. Primary keys define identity; foreign keys define relationships.
Candidate Response Strategy & Interview Tips
- Start with a concise one-sentence summary: Deliver a direct, confident answer first before expanding into nuances.
- Demonstrate real-world trade-offs: Discuss where this approach excels and when you would avoid it in production systems.
- Discuss complexity & edge cases: Proactively explain time/space complexity or boundary conditions (null values, scale limits).
- Prepare for interviewer follow-ups: Technical hiring panels frequently probe deeper into concurrency, backward compatibility, or alternative libraries.