PRIMARY KEY Constraint in SQL
Introduction
The SQL PRIMARY KEY constraint declares the selected identity of every row in a table. Primary-key values must be unique and non-null, allowing applications, relationships, updates, deletes, replication, and audits to refer to one row without ambiguity.
CREATE TABLE employees (
employee_id BIGINT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL
);
The constraint does more than combine UNIQUE and NOT NULL behavior. It communicates that employee_id is the table's principal row identity. Selecting that identity requires understanding row grain, candidate keys, stability, key width, generation, relationships, scale, exposure, and future migration.
Why Tables Need Row Identity
Names and descriptive attributes frequently repeat. Three employees can all be named John, and their departments can change. An instruction to update "John" is ambiguous, while employee_id = 102 identifies one row reliably.
Stable identity supports precise UPDATE and DELETE statements, foreign-key references, caching, event messages, audit trails, API resources, and synchronization. Without a key, duplicate-looking rows can be impossible to distinguish or repair safely.
Basic and Named Syntax
A single-column primary key can be declared beside the column or at table level. Explicit constraint names improve migration scripts and operational diagnostics.
CREATE TABLE customers (
customer_id BIGINT,
customer_name VARCHAR(100) NOT NULL,
CONSTRAINT pk_customers PRIMARY KEY (customer_id)
);
A common naming pattern is pk_<table_name>. The name should be stable, concise, and consistent with other constraint conventions.
Uniqueness and Non-Null Identity
A duplicate primary-key value is rejected because two rows cannot share one identity. NULL is also rejected because an unknown identity cannot reliably identify a row. These rules apply to INSERT and UPDATE operations.
Other columns can be required with NOT NULL or distinct with UNIQUE, but only the primary key is the selected principal identifier. A table can have several candidate keys and many UNIQUE constraints, yet only one PRIMARY KEY constraint.
PRIMARY KEY Versus NOT NULL and UNIQUE
NOT NULL requires presence. UNIQUE prevents duplicate protected values, with database-specific NULL behavior when the column is nullable. PRIMARY KEY provides non-null uniqueness plus row-identity semantics.
CREATE TABLE users (
user_id BIGINT PRIMARY KEY,
username VARCHAR(100) NOT NULL UNIQUE,
email VARCHAR(254) NOT NULL UNIQUE
);
All three values are distinct, but user_id is the chosen row identity. Username and email remain alternate candidate keys protected by independent business rules.
One Constraint Can Contain Multiple Columns
A table has one primary-key constraint, but that key can be composite. In an enrollment table, neither student nor course is individually unique; their combination can identify one enrollment relationship.
CREATE TABLE enrollments (
student_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
enrolled_at TIMESTAMP NOT NULL,
CONSTRAINT pk_enrollments
PRIMARY KEY (student_id, course_id)
);
The composite key permits one student in many courses and many students in one course while preventing the same pair twice. Every component is non-null. The key does not make either component individually unique.
Composite Key Use Cases
Natural composite keys commonly identify junctions and scoped facts: (order_id, product_id), (warehouse_id, product_id), (document_id, version_number), or (tenant_id, local_id). The combination must match the exact row grain.
If enrollment repeats by semester, (student_id, course_id) is too narrow; semester belongs in the identity or the model needs a separate enrollment ID with an appropriate UNIQUE rule. Key design changes when the business definition of one row changes.
Composite Key or Surrogate Key
The same relationship can use a generated ID as primary key while protecting its business combination separately.
CREATE TABLE student_courses (
student_course_id BIGINT PRIMARY KEY,
student_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
CONSTRAINT uq_student_course
UNIQUE (student_id, course_id)
);
The surrogate simplifies references from child tables and some ORMs. The composite alternative directly expresses relationship identity and avoids an extra column. Choose from stability, width, referencing needs, lifecycle, standards, and tooling rather than a universal slogan.
Natural Primary Keys
A natural key comes from the domain, such as a stable ISO country code, ISBN, or immutable certified identifier. It can be an excellent primary key when it is mandatory, compact, truly unique, stable, and safe to expose.
Many apparent natural keys fail one of those tests. Emails, phone numbers, names, government identifiers, and externally assigned codes can change, be corrected, reused, contain sensitive information, or be scoped differently. Confirm ownership and lifecycle before making business data the identity referenced everywhere.
Surrogate Primary Keys
A surrogate key is generated primarily for technical identity, such as customer_id, order_id, or product_id. It is usually compact, stable, and independent of mutable business attributes.
Surrogate identity does not prevent duplicate business entities. If customer email or employee number must be distinct, add the corresponding UNIQUE rule. A generated ID makes two rows technically different even when they represent the same real object.
Candidate and Alternate Keys
A candidate key is a minimal attribute set capable of identifying a row. Employee ID, employee number, and work email may all be candidates. The design selects one as primary and protects the remaining valid candidates with UNIQUE constraints.
Minimality matters. Adding unrelated columns makes a key wider and can permit duplicates the original candidate would reject. Record why a candidate was selected, especially when an external or historical requirement influences the choice.
Choose Stability Over Convenience
A primary key can appear in many child tables, caches, URLs, logs, data exports, event streams, and external mappings. Updating it can require coordinated changes across all those dependencies. A stable key reduces architectural coupling.
Human readability is usually less important than stability. Keep a technical primary key and a separate display or business number when users need a memorable order number, invoice sequence, or employee code.
Keep Primary Keys Minimal and Narrow
Key width affects the primary index, every referencing foreign key, and often secondary indexes. Wide strings and large composite keys increase storage, comparison cost, memory use, network payloads, and migration complexity.
This does not mean every key must be the smallest integer. Use enough capacity and the correct distributed strategy. It means avoiding descriptive columns and unnecessary components when a smaller stable identity exists.
Identity and Auto-Increment Generation
MySQL AUTO_INCREMENT, SQL Server IDENTITY, PostgreSQL identity columns, Oracle identity or sequences, and SQLite integer-rowid behavior can generate numeric values. Exact syntax and transaction behavior vary.
-- PostgreSQL example
CREATE TABLE orders (
order_id BIGINT GENERATED ALWAYS AS IDENTITY
PRIMARY KEY,
order_number VARCHAR(40) NOT NULL UNIQUE
);
Generation and primary-key declaration are separate concepts. A generated column is not automatically the key, and a primary key need not be generated.
Sequence Gaps Are Normal
Generated numeric keys do not guarantee consecutive values. Rollbacks, retries, caching, failed inserts, replication, and allocation strategies create gaps. Primary keys also do not guarantee chronological order or default SELECT order.
Never use a technical identity as a legal gapless invoice sequence unless a separate, carefully designed business-numbering process satisfies that requirement. Always use ORDER BY when result order matters.
Choose INT or BIGINT With Capacity Planning
Estimate lifetime row count, ingestion rate, retention, replication, and future consolidation. INT may be sufficient for bounded reference data, while high-volume events, orders, or telemetry may require BIGINT. Changing a heavily referenced key type later can be expensive.
Using BIGINT everywhere also increases storage and index width without always providing value. Consider signed ranges, vendor behavior, data-warehouse integration, and application language types. Choose deliberately rather than from habit.
Sequence Ownership and Permissions
When a primary key uses a sequence or database identity mechanism, the generator is part of the data contract. Migrations should create it with the table, connect the default correctly, grant only required permissions, and preserve its state during backup, restore, cloning, and failover.
Manual imports can insert values above the generator's current position and cause later collisions. After bulk loading or restoring data, compare the generator with the maximum stored key and advance it through the vendor-supported command. Ordinary application roles should not reset or arbitrarily change allocation state.
Sequence caching improves throughput but can create larger gaps after a restart. That is acceptable for technical identity. If the business requires audited, consecutive document numbering, implement it as a separate controlled process rather than weakening primary-key generation.
UUID Primary Keys
UUIDs support decentralized generation and reduce coordination between services, offline clients, or shards. They also enlarge keys and indexes relative to compact integers, increase payload size, and random UUID versions can reduce index locality.
Time-ordered UUID variants or other sortable distributed IDs can improve locality, but their ordering, privacy, and implementation characteristics differ. A UUID is not authorization and does not make a resource secret. Choose it for architecture, not appearance.
Internal and Public Identifiers
An application can use a compact internal key and expose a separate public identifier when enumeration risk, merging, multi-region generation, or external contract stability justifies it. The public ID should have its own UNIQUE rule.
Do not add two identities without a requirement because every mapping adds storage and operational complexity. Regardless of format, authorization must verify that the caller can access the requested object. Hard-to-guess IDs do not prevent insecure direct-object-reference flaws.
Primary Keys Define Parent Identity
Foreign keys normally reference a parent's primary key, giving child tables a stable relationship target.
CREATE TABLE order_items (
order_item_id BIGINT PRIMARY KEY,
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INTEGER NOT NULL,
CONSTRAINT fk_item_order
FOREIGN KEY (order_id) REFERENCES orders(order_id),
CONSTRAINT fk_item_product
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
Some databases also allow foreign keys to eligible UNIQUE candidate keys. Parent and child column types, collation, and semantics should match. Key-type decisions therefore propagate through the schema.
Primary Keys Should Rarely Change
Changing a referenced key can update many child indexes, lock large parts of the relationship graph, enlarge logs, invalidate caches, and confuse audit history. ON UPDATE CASCADE can maintain selected database relationships but cannot update every external dependency.
Use mutable business attributes as alternate keys when possible. If a primary-key migration is unavoidable, treat it as an architecture change with explicit mapping and staged rollout.
Primary Key and Index Are Related but Different
Databases usually enforce PRIMARY KEY with a unique index, but the logical constraint is not identical to physical clustering. SQL Server often creates a clustered primary-key index by default when no clustered index exists, but it can be nonclustered. InnoDB organizes table data around its clustered primary key. PostgreSQL and Oracle use their own storage and index behavior.
Do not assume primary-key order controls physical storage or query output across products. Review the target engine's implementation before using clustering and locality as selection criteria.
Key Width Affects Secondary Indexes
In clustered-storage designs, primary-key values may be included in every secondary index entry. A wide or random key can therefore multiply storage and I/O costs across the table. Even without clustering, foreign keys and joins repeatedly carry the identity.
Measure with realistic indexes and workloads. A wider distributed key may still be correct when it avoids central coordination; performance is one factor alongside identity and architecture.
Primary Key and Normalization
A clear key helps define functional dependencies and row grain. If a table has no answer to "what uniquely identifies one row?", the entity boundary may be wrong or the table may combine multiple facts.
For inventory, one row may mean one product in one warehouse, making (warehouse_id, product_id) a natural composite identity. If each inventory record has a surrogate key, preserve the same business uniqueness separately.
Primary Key Does Not Encode Every Rule
An order primary key prevents duplicate order IDs but does not guarantee a unique order number, valid customer, positive amount, or allowed status. Good design layers PRIMARY KEY, UNIQUE, FOREIGN KEY, NOT NULL, CHECK, defaults, and transactional rules.
Do not expect row identity to solve entity duplication. Business constraints remain necessary whether the key is natural, surrogate, numeric, string, or UUID.
Delete, Archive, and Identity Reuse
Deleting a row does not make its identity safe to reuse. Logs, events, backups, caches, exports, and external systems may still associate the old ID with the former entity. Reuse can make historical evidence point to a different object.
Soft-deleted and archived rows keep their primary keys. Restoration should preserve the same identity when it is the same entity. Define retention and anonymization separately from identity reuse.
Temporal and Versioned Records
Versioned data distinguishes entity identity from version identity. A document can have stable document_id while each revision uses (document_id, version_number) as a composite key or a separate document_version_id plus UNIQUE combination.
The current entity table and history table answer different questions. Do not overwrite historical versions or assign a new entity ID for every state change unless each row truly represents a separate entity.
Multi-Tenant Key Design
A shared-table SaaS model can use a globally generated ID or a composite key such as (tenant_id, local_id). Composite identity naturally expresses scope but widens every child relationship. A global ID simplifies references, while tenant-scoped UNIQUE rules still protect business numbers.
Whichever model is chosen, prevent cross-tenant references and include tenant scope in indexes and authorization. A globally unique primary key alone does not enforce tenant ownership.
Shards and Distributed Identity
Independent shards cannot safely allocate from one local integer sequence if records will later merge into one namespace. Distributed strategies include UUIDs, time-based sortable IDs, shard-prefixed ranges, database sequences with allocated blocks, or a centralized ID service.
Define whether identity must be global or only local to a shard. Consider clock behavior, collision handling, sort order, key width, privacy, offline generation, and disaster recovery. Distribution requirements belong in key design before data is created.
Partitioning and Primary Keys
Partitioned tables can restrict which primary or unique keys are enforceable. Some systems require the partition key to be part of every unique key because indexes are local to partitions. This can change a desired global identity into a composite one.
Choose partition strategy and row identity together. Do not add a partition column to a key without understanding how it changes foreign keys, APIs, and uniqueness semantics. Vendor capabilities and versions differ.
Replication and Change Data Capture
Replication, CDC, and synchronization use stable keys to identify inserted, updated, and deleted rows. Tables without keys may require full-row matching, generate inefficient replica identities, or make duplicate changes difficult to resolve.
Review database-specific replication requirements before omitting or changing a key. A key migration can affect connectors, event schemas, warehouses, and downstream state stores even when application SQL continues to work.
Do All Tables Need a Primary Key?
Persistent OLTP entity and relationship tables normally benefit from a primary key. Exceptions can include transient staging, append-only raw ingestion, or specialized analytical structures, but those cases still need a deliberate method for deduplication, lineage, and manipulation.
Without identity, exact duplicate rows can make one-row UPDATE or DELETE impossible. "The table is only temporary" should be verified against how long data lives and which processes must repair it.
Adding a Primary Key to Existing Data
Before adding a key, confirm that the candidate columns contain no NULL and no duplicate combinations.
SELECT candidate_id, COUNT(*) AS row_count
FROM legacy_records
GROUP BY candidate_id
HAVING candidate_id IS NULL OR COUNT(*) > 1;
Investigate why violations exist. Duplicate rows may be intentional historical snapshots whose identity includes time or version. Do not delete data until the row grain and business meaning are understood.
Adding a New ID to a Legacy Table
A high-level migration adds a nullable or generated ID, backfills a persistent value for every row, validates uniqueness and non-nullness, updates writers, creates mappings for consumers, adds the key, and then updates child references. Exact sequencing depends on database capabilities.
Do not assign IDs from unstable row ordering and assume they can be regenerated later. Once published, identity must persist. Preserve an old-to-new mapping for integrations and audits.
Changing an Existing Primary Key
A key change can affect foreign keys, indexes, views, procedures, ORMs, APIs, cache keys, URLs, events, ETL, replication, and warehouse dimensions. Use an expand-and-contract migration: introduce the new key, backfill and validate it, support both identities, migrate relationships and consumers, then retire the old key.
Test volume, locks, log growth, replication lag, rollback, and mixed application versions. A one-line DDL statement can represent a large system migration.
Primary Keys and ORMs
ORMs often prefer one generated scalar key and may make composite mappings more verbose. That is a tooling tradeoff, not proof that every table needs a surrogate ID. A surrogate can be reasonable, but preserve business uniqueness when the natural relationship must not duplicate.
Review generated migrations, equality semantics, identity maps, batching, and key-generation assumptions. The database contract should remain correct even when data is written outside the ORM.
APIs, Authorization, and IDOR
Primary keys often appear in API routes, but possession or prediction of an ID does not authorize access. Every request must verify ownership and permission. UUIDs reduce easy enumeration but do not prevent insecure direct-object-reference vulnerabilities.
Consider a separate public ID when internal topology, merge behavior, or external stability requires it. Avoid placing sensitive personal information directly in keys because identifiers spread widely through logs and integrations.
ETL and Warehouse Keys
Source-system primary keys may collide when data from several systems is consolidated. Preserve (source_system, source_id) as a UNIQUE business mapping and assign a warehouse surrogate key when needed.
Slowly changing dimensions can have one durable business entity key and multiple version rows. Fact tables should reference the correct dimension version according to the analytical model. Never assume a local source ID is globally unique.
Audit and Event Tables
Audit records and events need identities for deduplication, ordering context, correlation, and replay, even when they are append-only. An event ID can be globally generated, while aggregate ID and sequence number protect per-entity ordering with an additional UNIQUE rule.
Do not confuse event ID with event time or business entity identity. Each serves a separate purpose, and clocks alone are not reliable unique keys.
Primary Key Performance and Capacity
Estimate row count, insertion pattern, page locality, index depth, cache behavior, and child-key volume. Sequential integers often insert efficiently but central generation can be inconvenient in distributed systems. Random identifiers decentralize creation but may increase index fragmentation and write amplification.
Test representative concurrent writes and full index sets. Key performance cannot be evaluated from the primary index alone because secondary indexes and foreign-key tables amplify the choice.
String Primary Keys
A string can be a valid primary key when it is compact, mandatory, stable, and governed, as with a small reference code. String keys become costly when they are long, case-sensitive in surprising ways, locale-dependent, frequently corrected, or repeated through many child tables.
Define character set, collation, normalization, maximum length, and ownership before choosing one. URLs, names, descriptions, and formatted labels are usually poor identities because presentation or business wording changes. A separate stable ID can coexist with a UNIQUE string business code.
Business Numbers Are Not Always Primary Keys
Order, invoice, ticket, and account numbers may contain prefixes, check digits, year segments, tenant scope, or legal numbering rules. They can be generated later than the database row, corrected under controlled procedures, or reused only within a branch. A technical primary key allows the row to exist while the business number follows its own lifecycle.
Protect the number with the correct scoped UNIQUE constraint. Never assume a generated primary key also satisfies legal requirements for consecutiveness, format, issuance audit, or cancellation tracking.
Handle Generated-ID Collisions Explicitly
Database-managed sequences and identity columns coordinate allocation within their supported scope, while client-generated random or distributed IDs rely on algorithms and namespace design. Even extremely unlikely collisions must produce a controlled uniqueness violation rather than silent overwrite.
Generate a new candidate and retry only when the key is purely technical and the operation remains idempotent. A collision on an external or business identifier may indicate a duplicate request or conflicting entity and should not be hidden by assigning another value.
Entity Merges and Primary Identity
Production systems eventually discover duplicate customers, suppliers, or products that each already have a primary key. Merging them requires choosing a surviving identity, moving or reconciling relationships, preserving aliases, updating search and caches, and recording the retired-to-survivor mapping.
Do not simply change all IDs without considering audit trails, external references, historical exports, and events already published. An alias or merge-history table can preserve interpretability and redirect future lookups while keeping old evidence understandable.
Monitor Key Behavior After Deployment
Observe duplicate-key errors, sequence exhaustion, generation latency, hot index pages, fragmentation, failed foreign-key writes, replication lag, and ID-allocation service health. A rising duplicate rate can signal retries without idempotency, an incorrectly scoped generator, or a client reusing identifiers.
Alert well before numeric capacity is exhausted. Track the highest allocated value and growth rate, but account for caching and gaps. Distributed generators also need clock, node-ID, and failover monitoring according to their algorithm.
Testing a Primary Key
Test a valid insert, duplicate insert, NULL insert, and conflicting update. For composite keys, repeat individual components while varying the full combination, and verify every key component rejects NULL. Test concurrent generation and retry behavior.
Inspect actual schema metadata and indexes after migration. Integration tests should use the production database engine because identity, sequence, clustering, and transaction behavior differ from in-memory substitutes.
Safe Production Migration Checklist
- Define the exact row grain and candidate keys.
- Choose stable primary identity and required business UNIQUE rules.
- Profile NULLs, duplicates, key ranges, and data growth.
- Review key width, generation, tenant, shard, and partition scope.
- Inventory foreign keys, indexes, APIs, ORMs, ETL, CDC, and caches.
- Resolve data violations without destroying historical meaning.
- Backfill persistent values and validate them under active writes.
- Estimate locking, disk, log, WAL, redo, and replication impact.
- Test production-scale deployment and rollback or fix-forward steps.
- Add the constraint, migrate consumers, and monitor failures.
Common PRIMARY KEY Mistakes
- Leaving persistent operational tables without reliable identity.
- Using names, email, phone numbers, or mutable descriptive data blindly.
- Adding a surrogate key but omitting required business uniqueness.
- Choosing a composite key that does not match the row grain.
- Using an excessively wide key without considering child and index cost.
- Assuming auto-increment automatically means primary key or vice versa.
- Expecting generated numbers to be gapless, ordered, or chronological.
- Using technical IDs as legal document numbers or authorization controls.
- Choosing INT, BIGINT, or UUID everywhere without capacity analysis.
- Changing or reusing identities without tracing external dependencies.
- Assuming primary-key order determines physical or SELECT order.
- Ignoring partitioning, sharding, replication, and migration constraints.
Primary Key Design Checklist
- State what one row represents and how long that identity lives.
- Identify all minimal candidate keys and their stability.
- Separate technical identity from business numbers when needed.
- Keep the selected key non-null, minimal, stable, and appropriately sized.
- Enforce alternate candidate keys with UNIQUE constraints.
- Choose generation based on database and distribution architecture.
- Define tenant, shard, partition, and global identity scope.
- Avoid sensitive or mutable descriptive data in widely propagated keys.
- Verify foreign-key compatibility and deletion or update behavior.
- Measure primary, secondary, and child-index consequences.
- Document public-ID, API, audit, ETL, and replication mappings.
- Test duplicates, NULLs, concurrency, migration, and recovery.
Interview Questions and Answers
What is a primary key? A column or minimal column combination selected as the unique, non-null identity of each row.
Can a table have two primary keys? No. It has one primary-key constraint, though that key may contain several columns.
What is a composite primary key? Row identity formed by the unique combination of multiple non-null columns.
PRIMARY KEY versus UNIQUE? Both prevent duplicate keys, but the primary key is the selected non-null row identity; UNIQUE protects alternate keys.
Natural versus surrogate key? A natural key comes from the domain; a surrogate is generated primarily for technical identity.
Does auto-increment mean primary key? No. Generation and constraint declaration are separate.
Can generated key values have gaps? Yes. Gaps are normal after rollbacks, caching, retries, and allocation.
Does a primary key determine query order? No. Use ORDER BY for deterministic result order.
Why should a primary key be small? It appears in indexes and relationships, so width affects storage, memory, I/O, and join cost.
What must be checked before adding one? Row grain, NULLs, duplicates, stability, dependencies, index-build impact, and application compatibility.
Key Takeaway
A primary key is the durable identity contract for a table. It guarantees that every row can be addressed uniquely and cannot have an unknown identity. The strongest design begins with row grain and candidate keys rather than with an automatic preference for integers, UUIDs, or generated columns.
EFFECTIVE PRIMARY KEY = CORRECT ROW GRAIN
+ UNIQUE NON-NULL IDENTITY
+ STABILITY
+ MINIMAL WIDTH
+ SUFFICIENT CAPACITY
+ ARCHITECTURE-AWARE GENERATION
+ PRESERVED BUSINESS CONSTRAINTS
+ TESTED MIGRATION
Choose an identity that remains stable for the entity's lifetime, enforce alternate business keys independently, and trace how the value propagates through relationships and systems. Treat an existing-key change as a coordinated architecture migration, not a local DDL edit.