Advantages and disadvantages of having a composite primary key ...

Question

Instead of having a composite primary key (this table maintains a relationship between two tables that represents two objects [two tables]), it is assumed that the design has identity columns as the primary key and forces data restriction over two columns that represent data from primary key of entities.

For me, there is an identifier column for each relationship table that violates normalization rules.

• What are industry standards?
• What are the considerations you need to make before making a design decision?
• Which approach is right?

Thanks,
Smith

Answer 1

There are many tables in which you can have an identifier column as a primary key. However, in the case of the M: M relationship table that you describe, it is best practice NOT to use the new identifier column for the primary key.

The RThomas link in his commentary provides excellent reasons why it is best practice NOT to add an identifier column. Here is the link .

The ends will outweigh the pros in almost every case, but since you asked for the pros and cons, I also put a couple of unlikely pros.

against

• Adds complexity

• It can lead to duplication of relations if you do not apply uniqueness to the relation (which by default will make the primary key).

• Probably slower: db should support two indexes, not one.

Pros

All the pros are pretty sketchy

• If you had a situation where you needed to use the primary key of a relationship table as a join to a separate table (for example, an audit table?), The join would probably be faster. (As noted, adding and removing records is likely to be slower. Also, if your relationship table is a relationship between tables that themselves use unique identifiers, increasing the speed of using one identity column in a join versus two will be minimal.)

• For simplicity, an application may assume that each table it works with has a unique identifier as a primary key. (This is a poor design in the application, but you may not have control over it.) You could imagine a scenario in which it would be better to add some additional complexity to the database than complexity in such an application.

Answer 2

Minuses:

• Composite primary keys must be imported into all reference tables. This means larger indexes and more code to write (e.g., merges, updates). If you are systematic about using composite primary keys, this can become very cumbersome.
• You cannot update part of the primary key. For instance. if you use university_id, student_id as the primary key in the university table students, and one student changes the university, you must delete and recreate the entry.

Pros:

• Composite primary keys provide a general view of the constraint in a powerful and silent way. Suppose you have a table UNIVERSITY, a STUDENT table, a COURSE table, and a STUDENT_COURSE table (which the student follows, which course). If it is a restriction that you must always be a student at University A in order to follow the course of University A, then this restriction will be automatically checked if university_id is part of the composite keys of both STUDENT and COURSE.

Answer 3

You need to create all the columns in each table wherever it is used as a foreign key. This is the biggest flaw.