Why shouldn't in a ER Model an association have attributes with a primary key?

Why shouldn't in a ER Model an association have attributes with a primary key?

I know that later, an association is generally mapped to a database table with a primary key forme开发者_Python百科d by the foreign keys of the two (or more) entities connected to this association.

But from a pure ER modelling perspective, what is the reason? Is there any?

The problem comes when the association table is permitted to have multiple rows with the same combination of foreign keys. If that occures, then what meaning is to be applied to the various differing attribute values?

Sometimes this is what you want. It could be that two entities could have multiple simultanious relationships with differing attributes. But that would be very unusual.

A database design works best when it naturally models the real-world relationships, especially if it can automatically and structurally exclude bad or nonsensical data.

I agree with all of Jeffrey Whitledge's answer. Also:

Your question is related to the question of surrogate keys, which are generated for the purpose of indexing tables, for example SQL Server IDENTITY columns. You should stick with candidate keys, e.g. a compound key formed by the foreign keys, if you want to stay in the realm of "pure ER" modelling. If you're using candidate keys you will probably not be structurally excluding bad or nonsensical data as noted by Mr. Whitledge. This applies to associations as well as other types of entities.

That said, candidate keys do have some practical advantages depending on the environment you're working in. For example, MS Access handles surrogate keys much easier than correctly defined compound keys. There are other environments where being able to store a reference to a row as a 32 bit int is useful. I've actually seen presentations in which the application of surrogate keys to all entities was put forward as "best practice," which is taking things a bit far. The point is you do gain some simplicity from surrogates. You could use surrogates in conjunction with properly defined unique indexes on your candidate keys. Side note - it is valid to use an IDENTITY column as a primary key for entities you create such as invoices: in that case the IDENTITY column is actually not a surrogate.

If you're association does not have any attributes, then I doubt that adding a surrogate key will be of any benefit regardless of how much you like surrogate keys. If you're adding non-foreign key attributes to the relationship entity, you may see some benefit from adding a surrogate because you may have a need to select the relationship itself. In this case you should define a unique index on the foreign keys unless you want to allow duplicate relationships.

Who says it shouldn't? The model should have as many attributes and keys as required to represent whatever is being modelled. Every table (or entity) should have at least one key.