Composite Keys vs. Surrogate Keys in Junction Tables

1. The two basic patterns for junction tables at a glance

A classic many-to-many relationship, such as between users and roles or between products and categories, is modeled relationally through a dedicated junction table with two foreign keys. There are two established patterns for that table's primary key: the composite key, where both foreign key columns together form the primary key, and the surrogate key, where an additional technical ID column becomes the primary key while the two foreign keys are protected against duplicates through a separate unique constraint.

Both variants enforce the same business uniqueness rule, namely that a combination of both referenced entities may occur only once. The difference lies in the structural consequences for the index, storage footprint, referencing tables, and integration into application code.

2. Composite key: uniqueness enforced directly through the primary key

With a composite key made of both foreign keys, uniqueness is structurally guaranteed, without any separate unique index. The primary key index simultaneously serves as an efficient access path for queries filtering on both foreign keys together, and in many database systems also as a good access path for queries filtering only on the first part of the composite key, because the B-tree is sorted starting from the first column.

Since no additional technical ID column exists, there is also no storage cost for an extra index. For a very simple junction table without further attributes, whose sole purpose is marking the existence of a relationship, the composite key is often the more compact and direct solution.

-- Composite key: both foreign keys together
-- form the junction table's primary key
CREATE TABLE user_role (
    user_id BIGINT NOT NULL REFERENCES app_user(id),
    role_id BIGINT NOT NULL REFERENCES role(id),
    PRIMARY KEY (user_id, role_id)
);

3. Surrogate key: a stable ID for references from outside

As soon as a junction table is more than a pure existence marker, for example because it carries extra attributes like a timestamp, a quantity, or a status, a dedicated surrogate ID becomes valuable. This ID offers a single, stable reference point through which individual rows of the junction table can be addressed uniquely, regardless of how many columns define the business uniqueness.

This becomes especially relevant once another table needs to reference a specific row of the junction table in turn, such as an audit log recording when a particular user-role assignment changed, or a history table with version states. A foreign key targeting a composite key then requires two columns in the referencing table, a foreign key targeting a surrogate key needs only a single column.

-- Surrogate key: dedicated ID plus unique constraint
-- for business-level uniqueness
CREATE TABLE order_line (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id    BIGINT NOT NULL REFERENCES app_order(id),
    product_id  BIGINT NOT NULL REFERENCES product(id),
    quantity    INT NOT NULL CHECK (quantity > 0),
    unit_price  NUMERIC(10,2) NOT NULL,
    UNIQUE (order_id, product_id)
);

4. Practical example: junction table with extra attributes

An order line linking an order to a product is a typical example of a junction table with extra attributes. Quantity and unit price clearly belong to the individual assignment between order and product, not to either of the two referenced entities themselves. As soon as a cart or returns process needs to address individual order lines, for example to cancel a partial quantity or attribute a return to a specific line, a dedicated, stable ID for each row becomes practically indispensable.

With a pure composite key of order and product, every referencing operation would always have to carry both values, which quickly becomes unwieldy in multi-step processes like partial shipments or multiple return events per line. The surrogate ID decouples the technical reference from the business relationship and makes every individual line uniquely addressable.

5. Impact on ORM compatibility and application code

Many object-relational mappers are primarily designed around single-column surrogate keys and only support composite keys with additional configuration or a reduced feature set, for example around lazy-loading strategies, caching keys, or generic repository implementations that internally assume a single ID column. A composite key frequently forces special-casing at these points, weakening otherwise uniform handling of entities.

A surrogate key, by contrast, fits seamlessly into standard ORM patterns: every row has a single, unique ID, relationships can be modeled like any other entity, and generic reuse of repository or data access code works without special cases. The pure SQL model does not benefit directly from this decision, but the consistency and maintainability of the application code sitting on top of it does.

6. Impact on downstream foreign key relationships

A composite key as a foreign key target means every referencing table must redundantly carry both columns, increasing storage footprint and the width of every affected index. With several downstream tables each referencing the same junction table, this overhead adds up, and every referencing query must consistently carry both columns in the join predicate.

A surrogate key keeps every downstream foreign key relationship reduced to a single, compact column, regardless of how many columns define the junction table's own business uniqueness. That simplifies both the schema and every query joining across multiple levels of relationships.

7. Why the unique constraint on a surrogate key is not optional

Anyone choosing a surrogate key must not forget the separate unique constraint on the business foreign key columns. Without that constraint, the table completely loses its business-level integrity, because the mere existence of a technical ID does not prevent duplicate assignments of the same two entities. This mistake happens surprisingly often in practice, because primary attention during schema design falls on the primary key, and the extra constraint is easily overlooked.

The unique constraint on the business columns automatically creates its own index in most database systems, one that must be maintained in addition to the primary key index. That is a deliberate trade-off: more storage and a second index to maintain, in exchange for a stable, single-column reference for every downstream relationship.

-- Without this constraint, the surrogate key alone
-- does NOT prevent duplicate assignments
ALTER TABLE order_line
    ADD CONSTRAINT uq_order_product UNIQUE (order_id, product_id);

8. Performance differences for typical query patterns

For queries that filter exclusively on both foreign keys together, such as checking whether a specific user-role combination exists, the composite key is usually marginally more efficient, because no additional index lookup through a unique constraint is needed. For queries addressing a single row through its own ID, such as updating a specific order line, the surrogate key has the advantage, because access happens through a compact, single-column index.

In practice, tables with extra attributes almost always lean toward the second access pattern, because application code typically loads, updates, and references individual rows through their technical ID once the junction table carries more than a pure existence statement. The composite key's small performance edge for pure existence checks rarely outweighs this practical drawback.

9. Decision guide: which approach fits which situation

A composite key suits pure existence junction tables without extra attributes and without the need for other tables to reference individual rows of the junction table, such as a simple tag assignment or a plain permission mapping without further metadata. The composite key remains the more compact, direct solution here, without an unnecessary extra index.

As soon as extra attributes are added, a downstream table needs to reference individual rows, or the chosen ORM only supports composite keys in a limited way, a surrogate key with an accompanying unique constraint is the more robust choice. This decision should be made early in schema design, since switching from a composite to a surrogate key later requires migrating every already-existing downstream foreign key relationship.

10. Summary

11. FAQ: Composite vs. Surrogate Keys