RDBMS best practices - autoid for association table?

I have two tables, let's say they are called table A and table B. An item from table B can be present in multiple instances of A, and each A can contain multiple Bs so I have a table called a_b which links them together by their primary keys. My question is when I define this association table, should I have a primary key on the association开发者_如何学Python table? Or is it not needed? Just trying to avoid ending up on TDWTF, that's all :)

The primary key would be on the table A PK column and table B PK column in your association table. That way, you ensure you don't get any duplicate rows in your association table by accident.

One of the main purposes of primary keys is to guarantee referential integrity. That is, keep the data in your table clean, with no duplicates. The PK in this case will ensure you never have 2 duplicate rows in the association table.

I think you might want to use a primary key in order to show your intent. If for example you do not want
a, b
a, b
Then a primary key defined on A.a and B.b would make that more clear. If you don't care, but you have a,b and other fields, then adding a surrogate key as your primary key might help in giving you a uniform way to delete a row that you do not want. Otherwise you will have to delete where a=a and b=b and ?? then pick some field value from the row you want deleted. Whereas with a surrogate key you can just pick the row and say delete where mykey = 36 or something...

But really it depends on the business case. Many intersect tables have some kind of date range, or additional fields related to the relationship in addition to the keys of the two tables. Defining a primary key on the existing columns, a new surrogate key, some unique indexes, some constraints, or even having no indexes could all be valid courses of action depending upon your needs.

I would say definitely do whatever makes your intentions the most clear.

Not needed. Both keys should form the primary key of your association table. If you're going to be doing bidirectional navigation, consider adding an index with the keys reversed.

The primary key is needed always.

However, I'd say it depends what should it be. If you are going to use some sort of ORM systems (e.g. Hibernate) then it is best to have a surrogate identifier, while those two foreign keys (pointing to tables A and B) should form a unique index.

Also, if there would ever be a need to reference such a relationship from another table then this surrogate identifier would be really handy.