Composite Primary Keys

Besides Java, I do have a lot to do, as do we all, I presume, with Database and Database Administration and Database Design.

In my current job, I see a lot of code where a join table has a single column, an autogenerated id, for a Primary Key. And it's really annoying.

Due to lack of proper unique indexes, there are a lot of duplicate records in the table, only they're not duplicate because they have a unique Primary Key (an autogenerated id).

If the table is a perfect example of a way of joining two tables together, there is no reason not to have a combined Primary Key consisting of the two foreign keys used to join the other two tables together.

Example:

Table:

User

id

Table:

Widget

id

Bad Example:

Join Table:

User_Widget

id

user_id

widget_id

Good Example:

Join Table:

User_Widget

user_id

widget_id

In the good example, the constraints of the Primary Key take care of everything, it's quicker, it's more efficient, and it's always correct.