Why should not an association have attributes with a primary key in an ER model?

advertisements

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 formed 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?


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.