Use a composite primary key when the combination of two or more stable columns is the actual identity of the row. A junction table is the clearest example: one product can appear in many orders, but the same product should appear only once in a particular order.
Last updated: October 2, 2026.
CREATE TABLE order_items (
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders (order_id),
FOREIGN KEY (product_id) REFERENCES products (product_id)
);The composite key prevents duplicate order-product pairs and supports lookups beginning with order_id. Reverse lookups by product may still need a separate index on product_id.
Choose columns that represent stable identity
Good composite-key columns are required, compact, and unlikely to change. Relationship tables, localized values identified by (item_id, language_code), and version rows identified by (document_id, version_number) are common fits. SQL Server’s constraint documentation confirms that a primary key may contain multiple columns and that the complete combination must be unique.
Avoid volatile names, email addresses, or descriptive values. Updating a primary-key value also affects every referencing foreign key.
Use a surrogate key when references need simplicity
Add a single generated ID when the natural key is wide, frequently changes, or would make many child references cumbersome. Preserve the business rule with a separate UNIQUE constraint; a surrogate ID alone does not prevent duplicate real-world records.
Storage details matter too. MySQL notes in its data-size guidance that InnoDB stores primary-key columns in every secondary index, so a wide composite primary key increases index size. Its primary-key guide recommends a separate auto-increment key when no suitable compact natural key exists.
Continue with SQL joins, foreign-key lookup inserts, and multi-column index design.
