Primary vs Foreign Keys
Overview
Keys are the architectural foundation of Relational Databases. They dictate how tables connect and enforce strict data integrity. A PRIMARY KEY is the unique fingerprint for a row in a table (e.g., user_id); it guarantees uniqueness and automatically creates a hyper-fast index for searching. A FOREIGN KEY is a column in a child table that strictly references the PRIMARY KEY of a parent table, guaranteeing that you cannot create an Order for a User that does not physically exist.
Syntax
-- Standard Key Definitions during Table Creation
-- THE PARENT TABLE
CREATE TABLE customers (
-- The Primary Key: Automatically enforces UNIQUE and NOT NULL
customer_id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE NOT NULL
);
-- THE CHILD TABLE
CREATE TABLE orders (
order_id INT PRIMARY KEY,
amount DECIMAL(10,2) NOT NULL,
-- The Foreign Key Reference
customer_id INT,
-- Enforcing the strict relational link!
CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
-- Optional: What happens if the Parent is deleted?
ON DELETE CASCADE
);Common Pitfalls
- Using emails or usernames as a Primary Key (Natural Keys). While an email is unique today, what happens if the user wants to change their email tomorrow? You would have to scan the entire database and update every single Foreign Key that points to it, heavily locking the tables. Always use a meaningless, auto-incrementing Integer or UUID (Surrogate Key) as your Primary Key, and just add a
UNIQUEconstraint to the email. - Omitting Foreign Keys for 'Performance'. Some developers refuse to add Foreign Keys because they add a microscopic write-delay. Without them, your database will inevitably fill up with corrupted, orphaned data (Orders pointing to Users who were deleted years ago). Always enforce Referential Integrity.
Interview Questions
ON DELETE CASCADE do when attached to a Foreign Key constraint?If you delete a row in the Parent table (e.g., a Customer), the database will automatically and instantly hunt down every dependent row in the Child table (e.g., their Orders) and securely delete them as well, preventing orphaned data.
Real-World Example
Using UUIDs (Universally Unique Identifiers) as Primary Keys in distributed, microservice environments.
CREATE TABLE users (
-- Automatically generates a secure, unguessable V4 UUID string
-- e.g., '123e4567-e89b-12d3-a456-426614174000'
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
username VARCHAR(50)
);Check Your Knowledge
Test your understanding of Primary vs Foreign Keys with these quick questions.