SQL.Why is it bad to use composite key on many to many table ?

Short answer (interview-ready)

Using a composite primary key in a many-to-many table is often problematic because it tightly couples identity to business columns, complicates foreign keys, hurts ORM usability, and makes future schema evolution harder — even though it looks “relationally pure”.

That framing already sounds senior. Now let’s justify it.

The canonical many-to-many table

user_roles (
  user_id BIGINT,
  role_id BIGINT,
  PRIMARY KEY (user_id, role_id)
)

This is theoretically correct.
But theory ≠ production reality.

Why composite PKs hurt in practice

1️⃣ Identity is not stable

A primary key should represent row identity.

With a composite PK:

(user_id, role_id) = identity

But what if:

• Roles are merged
• Role IDs change
• You add versioning
• You add validity periods

Now the “identity” of the row changes.

Primary keys should not change.

2️⃣ Foreign keys become painful

The moment someone wants to reference this table:

audit_log (
  user_id BIGINT,
  role_id BIGINT,
  ...
)

You now need:

FOREIGN KEY (user_id, role_id)
REFERENCES user_roles(user_id, role_id)

Problems:

• Verbose
• Easy to break
• Harder to index
• Ugly in joins

Single-column FKs scale better organizationally.

3️⃣ ORMs hate composite primary keys (huge!)

In JPA/Hibernate:

• @EmbeddedId
• @IdClass
• Broken equals/hashCode
• Painful caching
• Awkward repositories

Example smell:

UserRoleId(userId, roleId)

Senior teams avoid this unless absolutely necessary.

4️⃣ Harder to evolve the table

Sooner or later someone asks:

• “Can we add assigned_at?”
• “Can we soft-delete roles?”
• “Can we add metadata?”

Now you want:

• Multiple rows per (user_id, role_id)
• History
• Flags

With composite PK:
❌ Impossible without redesign

With surrogate PK:
✔ Easy extension

5️⃣ Composite PK ≠ business rule clarity

The real business rule is:

A user cannot have the same role twice

That is a uniqueness rule, not an identity rule.

So this is conceptually cleaner:

id BIGSERIAL PRIMARY KEY,
UNIQUE (user_id, role_id)

Identity ≠ constraint.

6️⃣ Indexing & performance nuance

Composite PK creates:

PRIMARY KEY (user_id, role_id)

That index:

• Is ordered by user_id first
• May not fit your query patterns
• Forces additional indexes anyway

So you often end up with:

• Composite PK index
• Extra indexes

No real win.

The preferred production pattern ✅

user_roles (
  id BIGSERIAL PRIMARY KEY,      -- surrogate identity
  user_id BIGINT NOT NULL,
  role_id BIGINT NOT NULL,
  UNIQUE (user_id, role_id)      -- business rule
)

Benefits:

• Stable identity
• Simple foreign keys
• ORM-friendly
• Easy to extend
• Business correctness enforced

This is not junior laziness — it’s deliberate.

---

When composite PK is acceptable (be honest)

Composite primary keys are OK when:

• Table is purely associative
• No other table will reference it
• No extra attributes ever
• No ORM (or you’re brave)

Example:

• Pure link table in a reporting schema

Even then — many teams still avoid it.

---

Interview traps ❌

• “Composite keys are always bad” ❌
• “Surrogate keys are always better” ❌
• “ORMs can handle it fine” ❌

Correct stance:

Composite keys are relationally valid, but often operationally expensive.

---

One-sentence senior answer (perfect)

Composite keys in many-to-many tables couple identity to business columns, complicate foreign keys and ORM usage, and make future evolution harder, so in production we usually prefer a surrogate key with a composite UNIQUE constraint.