Advantages and disadvantages of having composite primary key

Question

Instead of having a composite primary key (this table maintains the relationship between the two tables which represents two entities [two tables]), the design is proposed to have identity column as primary key and the unique data constraint is enforced over two columns which represents the data from the primary key of entities.

For me having identity column for each relationship table is breaking the normalisation rules.

• What is the industry standards?
• What are the considerations to make before making the design decision on this?
• Which approach is right?

Answer 1

There are lots of tables where you may want to have an identity column as a primary key. However, in the case of a M:M relationship table you describe, best practice is NOT to use a new identity column for the primary key.

RThomas's link in his comment provides the excellent reasons why the best practice is to NOT add an identity column. Here's that link.

The cons will outweigh the pros in pretty much every case, but since you asked for pros and cons I put a couple of unlikely pros in as well.

Cons

• Adds complexity

• Can lead to duplicate relationships unless you enforce uniqueness on the relationship (which a primary key would do by default).

• Likely slower: db must maintain two indexes rather than one.

Pros

All the pros are pretty sketchy

• If you had a situation where you needed to use the primary key of the relationship table as a join to a separate table (e.g. an audit table?) the join would likely be faster. (As noted though--adding and removing records will likely be slower. Further, if your relationship table is a relationship between tables that themselves use unique IDs, the speed increase from using one identity column in the join vs two will be minimal.)

• The application, for simplicity, may assume that every table it works with has a unique ID as its primary key. (That's poor design in the app but you may not have control over it.) You could imagine a scenario where it is better to introduce some extra complexity in the DB than the extra complexity into such an app.

Answer 2

You have to create all the columns in each tables wherever it is used as foreign key. This is the biggest disadvantage.

Answer 3

Cons:

• Composite primary keys have to be imported in all referencing tables. That means larger indexes, and more code to write (e.g. the joins, the updates). If you are systematic about using composite primary keys, it can become very cumbersome.
• You can't update a part of the primary key. E.g. if you use university_id, student_id as primary key in a table of university students, and one student changes university, you have to delete and recreate the record.

Pros:

• Composite primary keys allow to enforce a common kind of constraint in a powerful and seemless way. Suppose you have a table UNIVERSITY, a table STUDENT, a table COURSE, and a table STUDENT_COURSE (which student follows which course). If it is a constraint that you always have to be a student of university A in order to follow a course of university A, then that constraint will be automatically validated if university_id is a part of the composite keys of both STUDENT and COURSE.