Should Many to Many Tables Have a Primary Key?

Question

If I have two objects that have a many-to-many relationship, I would typically model them in my database schema with a many-to-many table to relate the two. But should that many-to-many table (or "join table") have a primary key of its own (integer auto-incremented)?

For example, I might have tables A and B, each with an ID, and a table called A_B that has a foreign key tuple of (A_ID, B_ID). But should A_B have a primary key auto-incremented ID column of its own, or not?

What are the advantages and disadvantages of adding it? I personally like natural keys for many-to-many joins. But what added benefit would a primary key add?

Answer 1

I have done it both ways. Sometimes it is beneficial for adding a feature down the road. For instance, if there was ever a time that a row in the table would ever contain anything more than just the 2 id's. If you don't lack space I would put one in there just because it can't hurt. Sometimes it can interfere with ORM tools like hibernate or ADO.NET but that is minor.

So to sum it up... PROS 1. Allows potential future growth.

CONS 1. Space 2. Confuses some ORM tools.

Answer 2

The term "join table" is often used but I don't think I've ever seen it properly defined or explained. Personally I avoid using that term. As I understand it, a "join table" means any table with two foreign keys (or possibly more than two?).

I think the criteria for selecting keys in a table with more than one foreign key should be much the same as in any other table. Ask yourself what dependencies you need to enforce, what is unique and irreducible. Select keys on the criteria of Familiarity, Stability and Simplicity. Add surrogate keys only when you have a good reason to.

Answer 3

It doesn't really provide anything useful. Keep in mind the purpose of a key, which is to uniquely refer to "something." An association table like this isn't by itself "something" but is rather persistence structure for two other "somethings" which already have keys. Outside of the persistence medium (the database) it doesn't have a meaning and shouldn't even really exist or be known (such as in the business domain), so there would (should) never be a reason to refer to it by its own ID.

Answer 4

Such a surrogate key adds nothing except overhead.

Use the natural keys, make them a composite primary key if you care about duplication in this table.

To expand:

In the application, this key will be meaningless and will remain unused.

In the database, it will have no function, as you can't reasonably use it in a query for any type of meaningful result.

It can't reasonably be used as a foreign key either.

Answer 5

I agree with everything Oded said except

"It can't reasonably be used as a foreign key either."

In this case it's a pick your poison, the mapping table absolutely can be a parent, it's just a matter of the child using a multicolumn FK or not.

Take a simple case of Car and color. Each Year Auto Makers have a certain pallet of colors and each model only comes in limited number of those colors. Many - Many :: Colors to Cars models

So now design the Order table where new cars orders are stored. Clearly Color and Model will be on the Order table. If you make a FK to each of those tables, the database will permit an incorrect model/color combination to be selected. (Of course you can enforce this with code, you can't do so declaratively.) If you make the parent be the many:many table, you'll only get combinations that have been specified.

SO would you rather have a multicolumn FK and point to a PK built on both ModelID and ColorID or do you want a single column FK?

Pick your poison.

EDIT

But if it's not a parent of something, no table needs a surrogate key.