What is the difference between a primary key, foreign key, and unique key?
All three are constraints that enforce uniqueness or referential integrity, but each plays a different role.
Primary key — uniquely identifies each row. One per table. NOT NULL and unique. Automatically indexed; usually the table's clustered index in InnoDB.
Foreign key — column(s) that reference the primary key of another table. Enforces referential integrity; can cascade on update/delete.
Unique key — ensures all values in the column(s) are distinct. Can have multiple per table. Allows one NULL (or many, depending on engine).
-- Primary Key
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL
);
-- Foreign Key with CASCADE
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
total DECIMAL(10,2),
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
-- Composite Key
CREATE TABLE enrollments (
student_id INT,
course_id INT,
enrolled_at DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);users table: id is PRIMARY KEY (unique, not null, auto-increment). username and email are UNIQUE (can have at most one NULL each). orders: user_id is a FOREIGN KEY referencing users.id. ON DELETE CASCADE means deleting a user deletes their orders. enrollments: composite PRIMARY KEY (student_id + course_id) — one student can enroll in many courses, but not the same course twice.
Primary = unique + not null + one per table. Unique = unique + allows one null + many per table. Foreign = references another table's primary key.
Know CASCADE vs RESTRICT on delete. Composite key for many-to-many junction tables.