Interview › Databases & Caching
What is the difference between a primary key, a unique key, and a foreign key?
Databases & Caching · Intermediate level
Answer
A primary key uniquely identifies each row and cannot be null. A unique key enforces uniqueness for a column or column set. A foreign key enforces that values in one table refer to valid rows in another table.
Technical explanation
Primary keys identify rows and are often referenced by foreign keys.
Unique constraints enforce business uniqueness such as email or tenant plus name.
Foreign keys protect referential integrity but need indexing and migration planning on large tables.
Hands-on example
Example schema:
CREATE TABLE customers (id uuid PRIMARY KEY, email text UNIQUE NOT NULL);
CREATE TABLE orders (id uuid PRIMARY KEY, customer_id uuid NOT NULL REFERENCES customers(id));
Add an index on orders(customer_id) for join and delete/update performance.
Check how well your resume matches the role with our free resume checker— match score, ATS check, and the skills you're missing.
More Databases & Caching interview questions
- What is Amazon RDS, and what does it manage for you versus self-managed databases?
- What database engines does RDS support?
- What is the difference between RDS and Aurora?
- What is Multi-AZ in RDS, and how does automatic failover work?
- How long does an RDS Multi-AZ failover typically take, and what triggers it?
- What is the difference between Multi-AZ and a read replica?
- When would you use a read replica, and can it become a standalone database?
- Can a read replica be in a different region, and why would you do that?