Composite Keys

Concepts: dmCompositeKeys

A composite key uses multiple columns together to uniquely identify a row. The most common use case is a junction table for a many-to-many relationship. An enrollment table linking students to courses has a composite PK of (student_id, course_id). Neither column is unique alone, but the combination is. Composite PK vs Surrogate PK in Junction Tables Neither approach is universally correct. If the junction table is purely a link (no extra attributes), a composite PK is cleaner. If the junction table becomes an entity in its own right (e.g., enrollments with grades and dates), add a surrogate key and keep the composite as a UNIQUE constraint. Composite Keys in Slowly Changing Dimensions In SCD Type 2, every historical version of a dimension row needs a unique identifier. One approach is a co