beginner

Primary Key Constraints in SQL

By DataPlayArena Technical Review BoardUpdated 2026-07-208 min readRuntime Verified

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 UNIQUE and NOT 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:

sql
-- 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:

sql
-- 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 INT Instead of BIGINT: 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 use BIGINT for 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:

  1. 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.
  2. 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.
  3. 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 BIGINT clustered primary key with an external UUID candidate key.

Interactive Practice Challenge

Challenge: Inventory Stock Ledger Primary Key

Design a table warehouse_inventory that tracks product stock across physical warehouses. Define a composite primary key consisting of warehouse_id and sku_code, and include quantity_on_hand with a check constraint ensuring stock is non-negative.

DPA

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.