Should many to many tables have a primary key?
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
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.