🔑Primary Key

NEW

Also known as: PK

A column or set of columns that uniquely identifies each row in a relational table.

DESCRIPTION

A primary key enforces entity integrity by ensuring each row is uniquely identifiable. It prevents duplicate records and often underpins indexing and referential integrity. Primary keys can be natural or surrogate and may be composite across multiple columns. Good key design improves join performance, data quality, and maintainability. Choosing stable, minimal, and non-null attributes is best practice.

ALTERNATIVE DEFINITIONS

Wikipedia

A primary key is the choice of a candidate key that is to be used to uniquely identify each tuple in a relation.

Formal relational definition focusing on unique identification.

Source

PostgreSQL Documentation

A primary key indicates that a column or group of columns can be used as a unique identifier for rows in the table.

Vendor-neutral relational explanation with implementation context.

Source

MySQL Reference Manual

A PRIMARY KEY is a UNIQUE index where all key columns must be defined as NOT NULL.

Implementation-specific constraint definition focusing on uniqueness and NOT NULL.

Source

RELATED TERMS

Unique Constraint Surrogate Key Natural Key Composite Key Index Referential Integrity Feature Scaling Entity Integrity Relational Model

COMPETING TERMS

Foreign Key

RELATIONS

• Broader: Key

KNOWLEDGE AREAS

Data Modeling Data Architecture Data Storage & Operations Data Engineering

TARGET AUDIENCE

Data Modeler Data Engineer Data Architect Software Developer

EXTERNAL SOURCES

• Wikipedia - Primary key
• PostgreSQL - Constraints including primary keys
• Microsoft Learn - Creating primary keys in SQL Server
• SQLite - CREATE TABLE primary key syntax

TOP QUESTIONS

Why is a Primary Key important?

It guarantees entity integrity and enables reliable joins, preventing duplicate rows and ensuring each record is uniquely identifiable.

Category: Relevancy

Should I use a natural or surrogate Primary Key?

Surrogate keys are stable and simple; natural keys reflect business meaning but may change. Choose based on stability and governance.

Category: Opinion

Can a Primary Key be composite?

Yes. A composite key uses multiple columns to uniquely identify a row when no single column suffices.

Category: Definition

Is a Primary Key automatically indexed?

Most RDBMSs create an index for primary keys to enforce uniqueness and speed joins, though behavior may vary.

Category: BestPractice

Can a Primary Key be NULL?

No. Primary key columns must be NOT NULL to uniquely identify rows.

Category: Definition

What is the difference between Primary Key and Unique Constraint?

Both enforce uniqueness, but a table has only one primary key, often used for referential integrity; unique constraints can be multiple.

Category: Comparison

How does a Primary Key relate to Foreign Keys?

Foreign keys reference primary (or unique) keys in another table to enforce referential integrity.

Category: Definition

What are common mistakes with Primary Keys?

Using mutable fields, overwide composite keys, or non-unique values harms performance and data integrity.

Category: Mistakes

How do Primary Keys impact performance?

They improve join performance and index lookup speed, but overly large keys increase index size and write overhead.

Category: Performance