Primary keys and unique identifiers explained
primary key definition, natural key, surrogate key, composite key, NULL constraint, uniqueness guarantee, auto-increment vs UUID
The Primary Key
Every table needs a primary key (PK): one or more columns whose values uniquely identify each row. No two rows can share a PK value, and no PK column can be NULL.
Natural vs Surrogate Keys
A natural key is a real-world value that is already unique — an email address, a passport number, an ISBN. Use it when it is stable and truly unique.
A surrogate key is an artificial identifier you generate — a sequential integer or a UUID. Use it when no natural key exists or when natural keys are long and mutable.
-- Surrogate key with auto-increment
CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL
);
-- Natural key
CREATE TABLE countries (
iso_code CHAR(2) PRIMARY KEY, -- 'US', 'IN', 'DE'
name VARCHAR(100) NOT NULL
);Composite Keys
Sometimes no single column is unique, but a combination is. An enrollment table might use (student_id, course_id) as a composite PK — a student can appear many times, a course can appear many times, but the pair must be unique.
Prefer surrogate keys for large tables. Composite PKs propagate into every foreign key that references them, making joins verbose and migrations harder.