---
title: "Composite key"
description: "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."
canonical: "https://motherduck.com/glossary/composite-key/"
related:
  - title: "Constraints | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/constraints/"
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "CREATE INDEX | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/create-index/"
---

# 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.

<glossary-callout guide="duckdb-cheatsheet-full" />

### Example

```sql
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.