Composite key
A composite key is a primary key made up of two or more columns whose combined values uniquely identify a row, used when no single column is unique on its own.
Overview
A composite key (also called a compound key) combines multiple columns into a single primary key, because no individual column is unique by itself but the combination is. A classic example is a bridge table linking students to courses: neither student_id nor course_id alone identifies a row, but the pair (student_id, course_id) does.
Composite keys show up often in junction/bridge tables that resolve many-to-many relationships, in fact tables at a fine grain (e.g., order_id + line_number), and in time-series data where uniqueness requires both an entity identifier and a timestamp.
Example
Copy code
CREATE TABLE enrollments (
student_id INTEGER,
course_id INTEGER,
enrolled_on DATE,
PRIMARY KEY (student_id, course_id)
);
CREATE TABLE fact_order_lines (
order_id INTEGER,
line_number INTEGER,
product_key INTEGER,
quantity INTEGER,
revenue DECIMAL(10,2),
PRIMARY KEY (order_id, line_number)
);
SELECT order_id, SUM(revenue) AS order_total
FROM fact_order_lines
GROUP BY ALL;
Composite keys are also used as composite foreign keys, referencing another table's composite primary key column-for-column, when a relationship's uniqueness genuinely spans multiple columns.
Trade-offs
Composite keys can make joins more verbose — every join needs to match on all key columns — and can be less efficient to index than a single narrow surrogate key. Many warehouses introduce a single surrogate key even for entities that would naturally have a composite key, purely to simplify downstream joins, keeping the original columns as a UNIQUE constraint instead.
DuckDB angle
DuckDB supports multi-column primary and foreign keys directly with the PRIMARY KEY (col1, col2) syntax shown above, and enforces uniqueness across the combination. For analytical queries, joining on a composite key works the same as joining on a single key — you just list each column pair in the JOIN ... USING or ON clause.
Related terms
A primary key uniquely identifies each database row. Learn SQL definitions, examples, UUID vs auto-increment best practices, and how to fix constraint errors.
foreign key →A foreign key links one table to another by referencing its primary key. Learn how foreign keys enforce referential integrity, with SQL syntax, composite key examples, and data warehousing use cases.
UNIQUE constraint →A UNIQUE constraint ensures that all values in a column, or combination of columns, are distinct across every row in a table.
Surrogate key →A surrogate key is a system-generated identifier — typically a sequential integer or UUID — assigned to a row that has no business meaning of its own, used as the primary key in dimension and fact tables.
Natural key →A natural key is an identifier that comes from real-world business data — like an email address, SSN, or product SKU — as opposed to a surrogate key generated purely for database use.
Data normalization →Data normalization is the process of structuring relational tables to reduce data redundancy and prevent update anomalies, typically by applying a sequence of rules called normal forms.
FAQS
Composite keys work well for bridge/junction tables and fine-grained fact tables where the natural uniqueness genuinely spans two or more columns. Many teams still add a single surrogate key on top for simpler downstream joins, keeping the composite columns as a unique constraint.
