Primary Key in SQL

Introduction

A primary key is the column, or combination of columns, selected to provide the main identity of every row in a relational table. Its value must be unique, and every row must have a value. These rules allow the database, applications, and related tables to refer to one precise record without depending on descriptions that may repeat or change.

Suppose three employees are named John and work in the same department. Name and department cannot tell an UPDATE which John should receive a salary change. Employee_id values 101, 102, and 103 create three distinct identities. A predicate using employee_id 102 can safely target one row.

Primary keys also anchor relationships. Orders can reference Customers.customer_id, OrderItems can reference Orders.order_id, and a Profile can reuse User.user_id as both its primary key and foreign key. Choosing an appropriate primary key therefore affects integrity, schema size, indexes, joins, migrations, APIs, and the long-term stability of a system.

What Is a Primary Key?

A primary key is a candidate key chosen as the table's principal identifier. A candidate key is a minimal set of attributes that uniquely identifies a row. A table can have several candidate keys, but it has one primary key constraint. That one constraint may contain one column or multiple columns.

STUDENT
student_id | student_name | city
-----------+--------------+--------
101        | John         | Chicago
102        | Alice        | Dallas
103        | David        | Chicago

Primary key: student_id

Student_id identifies the row even when names or cities repeat. The key represents database identity, not necessarily something meaningful to a user.

Why Tables Need Reliable Identity

A reliable identifier makes single-row reads, updates, deletes, and relationships unambiguous. Without one, duplicate-looking rows cannot be distinguished, applications may update several records by mistake, and child tables have no stable value to reference.

A primary key also expresses a rule that the database can enforce under concurrency. Two sessions cannot successfully insert the same primary-key value merely because each checked for it before the other committed. The constraint provides the final atomic guarantee.

Primary Key Rules

The two defining rules are uniqueness and non-null identity. No two rows may share the complete primary-key value, and no primary-key column may be NULL. A table has one primary-key constraint, but a composite constraint can include several columns.

Duplicate Values Are Rejected

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);

INSERT INTO students VALUES (101, 'John');
INSERT INTO students VALUES (101, 'Alice'); -- rejected

The second insert attempts to reuse 101. The database rejects it even though the student_name differs, because the primary key states that student_id alone defines row identity.

An application should handle this constraint violation appropriately. Silently generating a different ID may hide a duplicate request, while reporting a generic server error may confuse users. The correct response depends on whether the operation is expected to be idempotent and who supplied the key.

NULL Values Are Rejected

NULL represents an absent or unknown value. A row cannot have an unknown main identity, so every column participating in a primary key is effectively non-null. A composite key cannot be partly NULL.

INSERT INTO students (student_id, student_name)
VALUES (NULL, 'Alice'); -- rejected

This differs from many UNIQUE constraints, whose treatment of NULL can vary by database product. Primary-key identity is consistently required.

Column-Level Syntax

A single-column primary key can be declared directly beside the column definition. This is concise and readable for simple tables.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

The declaration combines the column type and key constraint. Generated-value syntax, when needed, is separate and database-specific.

Table-Level Syntax

A table-level constraint lists the participating columns after all column definitions. It produces the same primary-key rule for a single column and is required for composite keys.

CREATE TABLE departments (
    department_id INT,
    department_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (department_id)
);

Teams may prefer table-level syntax because it keeps constraints grouped and supports consistent naming conventions.

Naming the Constraint

An explicit constraint name makes migration scripts, monitoring messages, and production troubleshooting easier to understand. Generated names may differ across environments or be difficult to recognize.

CREATE TABLE departments (
    department_id INT,
    department_name VARCHAR(100) NOT NULL,
    CONSTRAINT pk_departments
        PRIMARY KEY (department_id)
);

Use a predictable naming convention that remains unique within the scope required by the database product. The name identifies the constraint object; it does not change which columns form the key.

Single-Column Primary Keys

A single-column, or simple, primary key is common because it keeps references and joins compact. Customer_id, employee_id, product_id, and order_id are familiar examples.

CREATE TABLE products (
    product_id BIGINT PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

A simple key is not automatically surrogate or numeric. A stable country code could be a single-column natural primary key. Simple describes the number of columns, not the origin of the value.

Composite Primary Keys

A composite primary key contains multiple columns whose combination uniquely identifies the row. In Enrollment, neither student_id nor course_id is unique by itself, but the pair may identify one enrollment.

CREATE TABLE enrollments (
    student_id BIGINT NOT NULL,
    course_id BIGINT NOT NULL,
    enrollment_date DATE NOT NULL,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Student 101 can appear with courses 10 and 20, and course 10 can appear with students 101 and 102. Only the complete pair must remain unique.

When a Composite Pair Is Not Enough

The key must match the actual row grain. If a student can enroll in the same course in several terms, (student_id, course_id) is too restrictive. The key might include term_id, or the row might receive enrollment_id while a more precise UNIQUE constraint protects business rules.

Likewise, an employee can join the same project during several periods. A pair of employee_id and project_id identifies a relationship, but not each assignment event. Never choose a composite key merely because two foreign keys are present.

Primary Key and Candidate Keys

A table may have several minimal unique identifiers. User_id, username, and email can all be candidate keys if the business guarantees each is unique and non-null. Selecting user_id as primary does not make the other uniqueness rules disappear.

CREATE TABLE users (
    user_id BIGINT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE
);

Username and email become alternate keys implemented with UNIQUE constraints. This design separates internal identity from business lookup values.

Primary Key Versus UNIQUE

Primary Key Versus Foreign Key

A primary key identifies a row in its own table. A foreign key stores a value that must match an eligible key in a referenced table, thereby establishing a relationship. Foreign-key values can repeat and may be NULL when the relationship is optional.

CUSTOMERS
customer_id PRIMARY KEY
        |
        | referenced by
        v
ORDERS
order_id PRIMARY KEY
customer_id FOREIGN KEY

One table can have many foreign keys but only one primary-key constraint. A column can sometimes serve both roles, as in a shared-primary-key one-to-one relationship.

Entity Integrity

A primary key enforces entity integrity: each entity row has one unique, known identity. The rule allows descriptive attributes to change without making the entity become a different row.

Customer 101 may change name, email, phone, or address while remaining the same customer. Stable identity supports audit history, relationships, and application references across those changes.

One-to-One Relationships

A child table can reuse the parent's primary key as its own primary key and foreign key. This shared-key pattern permits at most one child row per parent.

CREATE TABLE user_profiles (
    user_id BIGINT PRIMARY KEY,
    biography VARCHAR(1000),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

User_profile cannot contain two rows for one user because user_id is unique, and it cannot reference a user that does not exist because it is also a foreign key.

One-to-Many Relationships

In a one-to-many relationship, the parent's primary key is copied into a repeatable foreign-key column on the many side. One customer_id can appear in many Orders rows, while each Order has its own order_id primary key.

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Many-to-Many Relationships

A many-to-many relationship is resolved through a junction table. Its primary key may be the combination of both foreign keys when each pair is allowed once. The Enrollment example uses student_id and course_id in this way.

If the association has repeated events, its own lifecycle, or references from other tables, a surrogate junction key may be more suitable. A separate UNIQUE constraint should still preserve any valid pair-level business rule.

Natural Primary Keys

A natural primary key comes from business data. A vehicle identification number, stable country code, or regulated identifier may qualify when its scope, uniqueness, and permanence are guaranteed.

CREATE TABLE countries (
    country_code CHAR(2) PRIMARY KEY,
    country_name VARCHAR(100) NOT NULL UNIQUE
);

Natural keys can simplify integration and carry recognizable meaning. They can also be long, mutable, sensitive, reused, or subject to changing formats. Validate the real lifecycle rather than assuming that familiar business data is permanent.

Surrogate Primary Keys

A surrogate primary key is an artificial identifier created to identify the row, such as an integer generated from a sequence or a UUID. It has no required business meaning and is designed to remain stable.

CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    email VARCHAR(150) NOT NULL UNIQUE,
    customer_name VARCHAR(100) NOT NULL
);

Customer_id provides internal identity, while email preserves business uniqueness. Surrogate keys do not replace natural candidate-key constraints.

Why Names and Emails Are Risky

Names repeat, change, and vary in formatting across cultures and systems. Phone numbers can change, be reassigned, or be absent. Email addresses may be unique today but change when a person changes employer or account.

If these values are primary keys, updates propagate through every referencing table and integration. A stable surrogate key plus a UNIQUE business constraint often separates identity from mutable contact data more safely.

Generated IDs Are Separate From Primary Keys

Primary key means unique, non-null main identity. Auto-increment, sequence, identity, or UUID generation describes how a value is produced. A primary key may be supplied by the application, derived from business data, or generated by the database.

Conversely, a generated column is not automatically a primary key unless the schema declares the constraint. Keep generation and integrity as separate design decisions.

Database-Specific Identity Syntax

Database products use different syntax for generated numeric values. MySQL commonly uses AUTO_INCREMENT, SQL Server provides IDENTITY, and modern PostgreSQL supports identity columns. The exact details for insertion, sequence ownership, and generated-value overrides differ.

-- Conceptual pattern; use syntax for the selected DBMS
CREATE TABLE customers (
    customer_id BIGINT GENERATED BY DEFAULT AS IDENTITY,
    customer_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (customer_id)
);

Treat vendor syntax as an implementation choice layered beneath the same primary-key rule.

Generated Values Can Have Gaps

Sequence and identity values are identifiers, not guaranteed row counts. Rollbacks, failed inserts, caching, concurrent allocation, replication, and manual operations can create gaps. A missing number does not prove that a row was deleted or that data is corrupt.

If the business requires a gapless regulated document number, design that process separately from the internal primary key, with appropriate transaction and audit rules. Do not overload technical identity with presentation sequencing.

Integer Primary Keys

Integers are compact, fast to compare, and easy to store in child foreign keys and indexes. Sequential generation usually provides good index locality. Their simplicity makes them common in centralized application databases.

Predictable integers should not be treated as authorization secrets. An API must still verify whether the caller is allowed to access customer 101 or order 5001. Hiding sequential values is not a substitute for access control.

UUID Primary Keys

UUIDs allow decentralized generation and an extremely large identifier space, which is useful across distributed services, offline clients, and data merges. They consume more storage than typical integers and can affect index locality depending on UUID version and physical representation.

Use a native UUID type when the database supports one rather than storing formatted text without need. The choice should consider generation location, insertion pattern, replication, exposure, storage, and workload.

String Primary Keys

Primary keys can be strings. Stable codes may be appropriate when their format, uniqueness, and lifecycle are genuinely controlled. String comparison rules, collation, case sensitivity, width, and normalization then become part of identity.

A long string key is copied into every referencing foreign key and related index. Measure that cost before selecting a business string simply because it looks readable.

Primary Keys and Indexes

Most relational database systems use a unique index or equivalent structure to enforce a primary key and support efficient lookup. The exact physical organization, clustering, and default behavior are product-specific.

A primary key is still a logical constraint, not merely an index. Dropping or replacing an index without preserving the constraint can weaken integrity. Likewise, an ordinary index on a column does not make that column a primary key.

Primary-Key Lookups

SELECT customer_id, customer_name, email
FROM customers
WHERE customer_id = 10500;

Because the key matches at most one row and is commonly indexed, this is a precise and efficient lookup. UPDATE and DELETE predicates also use primary keys to target one row, but applications should still check affected-row counts and transaction outcomes.

Primary Keys in JOINs

SELECT
    c.customer_name,
    o.order_id,
    o.order_date
FROM customers AS c
JOIN orders AS o
    ON o.customer_id = c.customer_id;

The join follows the parent primary key to the child foreign key. Compatible data types and supporting foreign-key indexes can improve common relationship queries. Constraints make the relationship explicit instead of relying on matching names alone.

Key Stability and Updates

Primary-key values can sometimes be updated, subject to references and database rules, but stable keys are strongly preferred. A change may require cascading through orders, payments, addresses, audit records, caches, events, and external integrations.

Even when ON UPDATE CASCADE is available, broad identity changes create operational work and can complicate replication or history. Model mutable business values as normal or alternate-key attributes whenever possible.

Adding a Primary Key to Existing Data

Adding a key to a populated table requires a data audit. Existing values must be non-null and unique, and the proposed columns must represent the correct row grain. Duplicate rows must be merged, corrected, or assigned distinct identities before the constraint can succeed.

SELECT proposed_id, COUNT(*)
FROM legacy_table
GROUP BY proposed_id
HAVING COUNT(*) > 1;

Large-table changes may lock data or build a sizable index. Plan migration, validation, rollback, and deployment according to the selected database's online-schema capabilities.

Changing a Primary Key Design

Migrating from a natural key to a surrogate key is a multi-table operation. A safe approach often adds the new identifier, populates it, creates matching child references, validates constraints, switches application reads and writes, and only then removes obsolete references.

Dual-write periods and backfills need consistency checks. Treat primary-key migration as a data-contract change, not a simple column rename, because consumers and integrations may depend on the old identity.

Primary Keys and Normalization

Candidate and primary keys help define functional dependencies. In Customer, customer_id determines name and email. In OrderItem, the composite key (order_id, line_number) determines product_id, quantity, and price.

Normalization examines whether non-key attributes depend on the whole key and nothing inappropriate. A poorly chosen key can hide repeated facts or make dependency analysis misleading.

Complete E-Commerce Example

CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    email VARCHAR(150) NOT NULL UNIQUE,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
    product_id BIGINT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    product_name VARCHAR(150) NOT NULL
);

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    order_number VARCHAR(50) NOT NULL UNIQUE,
    customer_id BIGINT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    order_id BIGINT NOT NULL,
    line_number INT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, line_number),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Customers, Products, and Orders use simple surrogate primary keys. Email, SKU, and order_number remain unique business identifiers. OrderItems use a composite primary key because line number has meaning within one order.

Primary Keys in APIs and URLs

Applications frequently expose a record identifier in routes such as /customers/101 or include it in request and response payloads. Using the database primary key can be convenient, but it creates a long-lived external contract. Changing the key strategy later may require redirects, compatibility logic, or client migrations.

A public resource identifier may therefore differ from the internal primary key. For example, a compact numeric customer_id can remain inside the database while a public_id is exposed externally and protected with a UNIQUE constraint. This separation can support data merging or reduce accidental coupling, but it adds another identifier that must be managed consistently.

Neither sequential nor random-looking IDs provide authorization. Every request must verify that the authenticated caller is permitted to access the referenced resource.

Primary Keys in Audit and History Tables

History tables often need to record several versions or events for one business entity. Reusing customer_id alone as the history table's primary key would permit only one row per customer. The historical grain requires another discriminator such as version_number, effective timestamp, or a separate audit_event_id.

CREATE TABLE customer_history (
    history_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    changed_at TIMESTAMP NOT NULL,
    changed_by VARCHAR(100) NOT NULL,
    snapshot_data VARCHAR(4000) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

The live customer's key remains a foreign key in each history row, while history_id identifies one recorded change. This illustrates why a primary key must identify the table's row grain rather than merely contain the most important business ID.

Primary Keys and Upsert Operations

Insert-or-update operations need a reliable conflict target. A primary key is a natural target when the caller knows the row's internal identity. A business UNIQUE key may be more appropriate when importing records identified by email, SKU, or an external reference.

Do not assume that every duplicate primary-key error should become an update. A reused ID may represent a programming defect, an old message, or data from another system. Upsert semantics should identify which candidate key establishes sameness and which fields may be changed safely. The exact SQL syntax differs among database products, but the identity decision is a domain rule.

Imports, Merges, and Identifier Collisions

Two independent databases can both contain customer_id 101 for different people. Copying rows directly into one database creates a primary-key collision even though each source was internally valid. Migration design may allocate new surrogate IDs and retain source_system plus source_id as a UNIQUE mapping key.

UNIQUE (source_system, source_customer_id)

Foreign keys in imported child rows must be translated through the same mapping. UUIDs can reduce collision risk for decentralized generation, but natural and alternate business keys still need reconciliation. Data merging demonstrates that uniqueness always has a scope: a value unique inside one table or system may not be globally unique.

Testing a Primary Key

Tests should insert valid distinct values, attempt a duplicate value, and attempt NULL for each participating column. Composite-key tests should prove that individual columns may repeat while the full combination cannot. Concurrent tests can verify that racing inserts do not create duplicates.

Relationship tests should confirm that valid foreign keys reference the primary key and invalid references fail. Migration tests should cover existing duplicates, missing values, key-generation behavior, and rollback. Performance tests should use realistic key distributions and table sizes.

Common Primary-Key Mistakes

Common mistakes include using a non-unique name, choosing mutable email or phone data, assuming a key must be an integer, or assuming it must be generated automatically. Another mistake is declaring two separate primary keys instead of one primary key plus alternate UNIQUE constraints.

Teams also select a composite pair that does not distinguish repeated historical events, use an excessively wide key throughout the schema, or add a surrogate key without preserving business uniqueness. A technical ID can coexist with duplicate real-world entities unless candidate-key rules are declared.

Finally, developers sometimes expose IDs and assume unpredictable values provide security. Authorization must be enforced independently of identifier format.

Primary-Key Selection Workflow

Define what one row represents
        |
Identify guaranteed candidate keys
        |
Check uniqueness and NULL rules
        |
Evaluate stability, size, privacy, and reuse
        |
Choose simple or composite identity
        |
Choose natural or surrogate implementation
        |
Declare alternate UNIQUE constraints
        |
Create foreign-key relationships
        |
Plan generation and supporting indexes
        |
Test duplicates, concurrency, and lifecycle changes

Interview Questions and Answers

Can a primary key contain duplicates or NULL? No. The complete value is unique, and every participating column is non-null.

Can a table have multiple primary keys? It has one primary-key constraint, which may contain multiple columns.

What is a composite primary key? One primary key formed from two or more columns whose combination uniquely identifies a row.

Is a primary key automatically an integer or auto-incremented? No. Type, generation, and primary-key identity are separate decisions.

What is the difference between PRIMARY KEY and UNIQUE? The primary key is the chosen main identity and disallows NULL. UNIQUE enforces additional candidate-key rules, with NULL semantics depending on the DBMS and column definition.

Can a primary key be a foreign key? Yes, particularly in shared-primary-key one-to-one relationships.

Practical Review Checklist

• Define the exact entity or event represented by one row.
• Choose a key guaranteed to be unique and non-null.
• Prefer a stable and minimal identifier.
• Use composite keys only when the full combination defines identity.
• Keep surrogate identity separate from business uniqueness.
• Select data types with storage and reference cost in mind.
• Do not confuse value generation with the primary-key constraint.
• Plan references and migrations before changing identity.
• Enforce authorization independently of identifier predictability.
• Test duplicates, NULL, concurrency, and related foreign keys.

Key Takeaway

A primary key is the selected main identity of a table row. It guarantees one unique, non-null key value per row and provides the stable reference used by queries, updates, deletes, relationships, and application code.

PRIMARY KEY
    = unique
    + non-null
    + main row identity
    + one constraint with one or more columns

The best primary key matches the true row grain, remains stable, and is efficient to reference. Natural, surrogate, integer, string, UUID, simple, and composite forms are all valid when chosen for the domain. Preserve alternate business keys with UNIQUE constraints, and remember that a primary key identifies the row while a foreign key connects it to related rows.