Should primary keys always be added to an innodb table?

Question

I have some innoDbs with only 2 int columns which are foreign keys to the primary keys of other tables.

E.g one table is user_items, it has 2 columns, userId, itemId, both foreign keys to user and item tables, set to cascade if updated or deleted.

Should I add a 3rd column to such tables and make it a primary key, or is it better the way it is right now, in terms of performance or any other benefits?

Answer 1

Adding a third ID column just for the sake of adding an ID column makes no sense. In fact it simply adds processing overhead (index maintenance) when you insert or delete rows.

A primary key is not necessarily "an ID column".

If you only allow a single associated between user and item (a user cannot be assigned the same item twice) then it does make sense to define (userid, itemid) as the primary key of your table.

If you do allow the same pair to appear more than once then of course you don't need that constraint.

Answer 2

You already have a natural key {userId, itemId}. Unless there is a specific reason to add another (surrogate) key, just use your existing key as primary.

Some reasons for the surrogate may include:

• Keeping child FKs "slimmer".
• Elimination of child cascading updates.
• ORM-friendliness.

I don't think that any of this applies to your case.

Also, please be aware that InnoDB tables are clustered, and secondary indexes in clustered tables are more expensive than secondary indexes in heap-based tables. So ideally, you should avoid secondary indexes whenever you can.