DataTerminology.comNEW
Also known as: Compound Key, Composite Primary Key
A unique identifier for a row formed by two or more columns used together.
DESCRIPTION
A composite key is a primary or unique key spanning multiple columns to ensure row uniqueness. It often models natural business keys, like (order_id, line_number). Composite keys can improve integrity but complicate foreign keys and change management. They interact with indexing, affecting query performance. Dimensional models typically avoid composite business keys in favor of surrogate keys. In OLTP, composite keys can enforce real-world constraints without introducing synthetic identifiers. Use them when the combined attributes are stable and minimal.
ALTERNATIVE DEFINITIONS
Wikipedia
In database design, a compound key is a key that consists of two or more attributes that uniquely identify an entity occurrence.
Defines uniqueness using multiple attributes for identification.
MySQL Reference Manual
A PRIMARY KEY can consist of multiple columns; in that case, the key is a composite key.
Vendor documentation explicitly supports multi-column primary keys.
PostgreSQL Documentation
A primary key is a column or a group of columns used to identify a row uniquely; multi-column primary keys are declared by listing multiple columns.
Explains multi-column (composite) primary keys in SQL DDL.
RELATED TERMS
Primary Key Candidate Key Foreign Key Unique Constraint Feature Scaling Entity Integrity Index Denormalization
COMPETING TERMS
Surrogate Key Natural Key
RELATIONS
- Broader: Key
KNOWLEDGE AREAS
Data Modeling Data Storage & Operations Data Architecture
TARGET AUDIENCE
Data Modeler Data Engineer Software Developer Data Architect Information Analyst
EXTERNAL SOURCES
- Wikipedia - Compound key
- MySQL - SQL syntax for composite keys
- PostgreSQL - Constraints including multi-column keys
- Oracle - Constraints and composite keys
TOP QUESTIONS
When should a Composite Key be used?
Use it when a natural business identifier spans multiple stable attributes and synthetic keys are unnecessary.
Category: Opinion
What are the downsides of a Composite Key?
It complicates foreign keys, indexing, and schema evolution if any component changes frequently.
Category: Risks
How does a Composite Key affect indexing?
Order of columns matters for index selectivity and performance. Choose the most selective leading column for typical queries.
Category: Best practices
Is a Composite Key compatible with surrogate keys?
Yes. Use a surrogate key for joins while preserving a composite natural key with a unique constraint.
Category: Management
What’s a common mistake with Composite Keys?
Including non-minimal or volatile attributes that can change, causing cascading updates.
Category: Mistakes
How do Composite Keys work in dimensional models?
They’re typically avoided for fact-to-dimension joins; surrogate keys simplify SCD handling and performance.
Category: Example
Do Composite Keys impact sharding?
Yes. Composite keys can be hashed or ranged, but careful selection affects distribution and hotspotting.
Category: Requirements
Can a Composite Key include nullable columns?
Primary keys cannot contain NULLs; unique composite keys may allow NULLs depending on database semantics.
Category: Testing
How do you reference a Composite Key with a foreign key?
Define a composite foreign key with the same ordered columns and compatible data types.
Category: Management