Primary Key Constraints in SQL
Overview
A PRIMARY KEY constraint uniquely identifies each physical record within a database table. It is the cornerstone of the relational model, enforcing both uniqueness and mandatory existence (NOT NULL). A table can possess at most one primary key. In modern database design, choosing between surrogate integer keys, auto-incrementing identities, UUIDs, or composite natural keys impacts table clustering, query performance, and indexing storage.
What You Will Learn
In this lesson, you will master:
- Primary Key Invariants: The dual enforcement of
UNIQUEandNOT NULL. - Surrogate vs Natural Keys: Auto-incrementing integers (
BIGINT IDENTITY) vs UUIDs vs business keys. - Composite Primary Keys: Defining keys across multiple columns in junction / association tables.
- Clustered Index Mechanics: How the primary key dictates physical on-disk row ordering in MySQL (InnoDB) and SQL Server.
- Primary Keys as Foreign Key Anchors: Referential integrity foundations.
Defining Primary Keys
1. Modern Identity & Auto-Incrementing Keys
Modern SQL standards recommend GENERATED ALWAYS AS IDENTITY over legacy vendor-specific types like PostgreSQL SERIAL:
-- Modern ANSI Standard Identity (PostgreSQL 10+, Oracle 12+, SQL Server)
CREATE TABLE orders (
order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id INT NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
total_amount NUMERIC(12, 2) NOT NULL
);
-- MySQL Auto-Increment Syntax
CREATE TABLE orders_mysql (
order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
total_amount DECIMAL(12, 2) NOT NULL
) ENGINE=InnoDB;
Composite Primary Keys (Junction Tables)
In many-to-many relationship tables, the primary key is composed of two or more foreign key columns combined:
-- Junction table connecting students and courses
CREATE TABLE course_enrollments (
student_id INT NOT NULL,
course_id INT NOT NULL,
enrollment_semester VARCHAR(20) NOT NULL,
grade VARCHAR(2),
-- Composite primary key
CONSTRAINT pk_course_enrollments PRIMARY KEY (student_id, course_id, enrollment_semester),
-- Foreign keys referencing parent tables
CONSTRAINT fk_enrollments_student FOREIGN KEY (student_id) REFERENCES students(student_id),
CONSTRAINT fk_enrollments_course FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
Surrogate vs Natural vs UUID Primary Keys: Architectural Trade-Offs
| Key Type | Example | Advantages | Disadvantages |
| :--- | :--- | :--- | :--- |
| BigInt Identity | 1, 2, 3... | • Tiny 8-byte footprint
• Monotonically increasing (zero B-Tree page splits)
• Fast joins | • Exposes business volume publicly
• Collisions during multi-region database merges |
| UUID v4 (Random) | a8098c1a-... | • Globally unique across microservices
• Safe to expose in public API URLs | • 16-byte storage overhead
• Random insertion causes severe B-Tree page splits and fragmentation |
| UUID v7 (Time-Ordered) | 018d3b76-... | • Globally unique
• Monotonically ordered by timestamp (eliminates index page splits) | • Requires modern library or DB support |
| Natural Key | SSN, TaxID | • Avoids extra surrogate column | • Natural values often change or are subject to privacy laws (GDPR) |
The Physical Storage Layer: Clustered Indexes
In relational database engines like MySQL (InnoDB) and Microsoft SQL Server, the primary key automatically acts as the Clustered Index:
- The entire data row (all columns) is physically stored in the leaf nodes of the primary key's B-Tree.
- Secondary indexes do not point to physical disk addresses; they store the primary key value as their lookup pointer.
- Therefore, having a wide, composite primary key (e.g. 5 string columns) bloats the size of every secondary index on that table!
Common Mistakes & Gotchas
- Using Modifiable Business Values as Primary Keys: Using email addresses or phone numbers as primary keys causes immense migration friction when a user updates their email. When the primary key changes, all referencing foreign keys in historical transaction tables must be updated.
- Using 32-bit
INTInstead ofBIGINT: A standard 32-bit signed integer caps out at $2,147,483,647$ rows. Rapidly growing transactional tables (clickstreams, audit logs, order items) will crash production when reaching the 2.1-billion-row integer overflow limit. Always useBIGINTfor transactional primary keys.
Interview Perspective
SQL Interview Question: Why is a monotonically increasing BIGINT preferred over a random UUID v4 for clustered primary keys?
Key Talking Points for Candidates:
- B-Tree Page Splits: In a B-Tree clustered index, monotonically increasing values are always appended to the rightmost leaf page. Random UUIDs insert unpredictably throughout the tree, forcing expensive disk page splits and fragmentation.
- Secondary Index Size: In InnoDB, every secondary index stores the primary key value. A 16-byte UUID doubles or triples secondary index memory overhead compared to an 8-byte
BIGINT. - Best Practice Solution: If global client-generated uniqueness is required, use UUID v7 (which prefixes the UUID with a millisecond timestamp for sequential ordering) or pair an internal
BIGINTclustered primary key with an externalUUIDcandidate key.
Interactive Practice Challenge
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.