Composite Keys

Concepts covered: Composite Keys, Junction Tables

A composite key is a primary key made of 2 or more columns, unique in combination where no single column is unique on its own. The database treats the combination as 1 value for enforcement: (product_id, tag_id) can appear once, while product_id 40817 may appear in 30 rows and tag_id 7 in 20,000. The shape appears wherever the entity being identified is a relationship between 2 other entities rather than a thing of its own.

The junction table

FreshCart tags products for search: organic, gluten-free, local. A product has many tags and a tag many products, a many-to-many relationship no foreign key on either side can hold: a product_id column on tags would allow 1 product per tag. The junction table product_tags resolves it with 1 row per product-tag pair and a foreign key to each parent.

Schema
  • products: product_id BIGINT PK, sku VARCHAR, product_name VARCHAR
  • product_tags: product_id BIGINT PK, tag_id BIGINT PK
  • tags: tag_id BIGINT PK, tag_name VARCHAR
  • product_tags.product_id references products.product_id
  • product_tags.tag_id references tags.tag_id

The composite key (product_id, tag_id) states the business rule directly: a product carries a given tag once. The grain is 1 row per product-tag pair, and the key is the grain written down. A surrogate product_tag_id here would still need UNIQUE (product_id, tag_id), or the same pair could land twice; that constraint is the composite key under another name.

When the relationship grows attributes

A junction table stays a bare pair until the business records something about the relationship itself, such as who applied a tag and when. The cost of a composite key shows once another table points at a row of product_tags, say a tag_reviews table of compliance checks on each assignment: its foreign key carries both product_id and tag_id, so every join on it is 2 columns wide, and so is the index behind it. At that width a surrogate starts to pay for itself.

The same pattern runs through dimensional modelling. A type 2 slowly changing dimension holds many versions of 1 customer, and the versions could be told apart by (customer_id, valid_from). The standard choice is a surrogate customer_sk per version, because 2.1 billion fact rows each store 1 key column instead of a natural key plus a date.

The partial dependency trap

If product_tags also stored product_name, that column would depend on product_id alone. That's a partial dependency, a non-key column depending on part of the key, and second normal form forbids it: a rename reaches products while 30 rows in product_tags keep the old name. Every non-key column in a composite-key table depends on the whole key, and anything depending on part of it belongs in that part's own table.

About this lesson

This section is part of the Keys & Identity lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.