SQL - many-to-many table primary key

Question

This question comes up after reading a comment in this question:

When you create a many-to-many table, should you create a composite primary key on the two foreign key columns, or create a auto-increment surrogate "ID" primary key, and just put indexes on your two FK columns (and maybe a unique constraint)? What are the implications on performance for inserting new records/re-indexing in each case?

Basically, this:

PartDevice
----------
PartID (PK/FK)
DeviceID (PK/FK)

vs. this:

PartDevice
----------
ID (PK/auto-increment)
PartID (FK)
DeviceID (FK)

The commenter says:

making the two IDs the PK means the table is physically sorted on the disk in that order. So if we insert (Part1/Device1), (Part1/Device2), (Part2/Device3), then (Part 1/Device3) the database will have to break the table apart and insert the last one between entries 2 and 3. For many records, this becomes very problematic as it involves shuffling hundreds, thousands, or millions of records every time one is added. By contrast, an autoincrementing PK allows the new records to be tacked on to the end.

The reason I'm asking is because I've always been inclined to do the composite primary key with no surrogate auto-increment column, but I'm not sure if the surrogate key is actually more performant.

Here's a silimar question posted on SO: stackoverflow.com/questions/344068/… — Tony
(Tried to add this to my previous comment but can't) Depending on the number of inserts you can also periodically rebuild your index to ensure it returns results quickly. In SQL Server you can also tweak the FILLFACTOR of the index to provide enough space for inserts before it has to move data around. — Tony
Doesn't the answer to this depend on what DBMS is used? I suspect MySQL will behave in a way in this case, SQL-Server slightly in another way etc. — Radu Murzea
Caveat: Without a specific database tag, much of what is said here is suspect. Different engines work differently! — Rick James

paxdiablo paxdiablo · Accepted Answer · 2010-02-03T07:20:42

With a simple two-column many-to-many mapping, I see no real advantage to having a surrogate key. Having a primary key on (col1,col2) is guaranteed unique (assuming your col1 and col2 values in the referenced tables are unique) and a separate index on (col2,col1) will catch those cases where the opposite order would execute faster. The surrogate is a waste of space.

You won't need indexes on the individual columns since the table should only ever be used to join the two referenced tables together.

That comment you refer to in the question is not worth the electrons it uses, in my opinion. It sounds like the author thinks the table is stored in an array rather than an extremely high performance balanced multi-way tree structure.

For a start, it's never necessary to store or get at the table sorted, just the index. And the index won't be stored sequentially, it'll be stored in an efficient manner to be able to be retrieved quickly.

In addition, the vast majority of database tables are read far more often than written. That makes anything you do on the select side far more relevant than anything on the insert side.

SQL - many-to-many table primary key

5 Answers