Composite Primary Keys in ERD Design

What is a Composite Primary Key?

A composite primary key uses two or more columns together to uniquely identify a row. No single column is unique on its own, but the combination is. This is most commonly used in junction tables for many-to-many relationships.

Junction Table Example

CREATE TABLE course_enrollments (
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  enrolled_at TIMESTAMP NOT NULL DEFAULT NOW(),
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

The composite key (student_id, course_id) ensures a student can enroll in a course only once. Neither column alone is unique — a student enrolls in many courses, and a course has many students.

Natural Composite Keys

Some entities have natural composite keys based on business rules:

Table	Composite Key	Reason
schedule_slots	(room_id, time_slot)	A room can only be booked once per slot
price_history	(product_id, effective_date)	One price per product per date
translations	(entity_id, locale)	One translation per entity per language

Composite PK vs Surrogate PK

Aspect	Composite PK	Surrogate PK (id SERIAL)
Uniqueness	Enforced by business columns	Enforced by auto-increment
ORM support	Some ORMs struggle with composite PKs	Universally supported
Index size	Larger (multiple columns)	Smaller (single integer)
Semantics	Self-documenting	Requires UNIQUE constraint separately
JOIN performance	Depends on column types	Fast integer joins

In the ERD Editor

When creating an entity with a composite primary key, check the PK checkbox on multiple columns. Both columns will be highlighted in gold in the diagram. The generated SQL will produce a PRIMARY KEY (col1, col2) constraint at the table level.

Best Practice

Use composite primary keys in junction tables and when the natural key is stable and meaningful. Use surrogate keys (id) when the natural key is too wide, unstable, or when ORM compatibility is a priority.