Is it ever a good idea to not have an 'id' primary key for a table?

Question

It seems to me like its always a good idea, but is there ever a case where you are better off not having this in table?

Answer 1

Normally, you should have some kind of a PRIMARY KEY.

There are some really really special situations when you don't want to, most notably heap-organized (non-clustered) log tables which don't have any indexes to speed up inserts.

Note that in MySQL, InnoDB tables cannot be heap-organized so this only concerns MyISAM tables.

Also note that a PRIMARY KEY can be composite, like in a many-to-many relationship table. You will not have an id column in such a table but you will have a composite primary key composed of the id columns of the tables related.

See this question for more details:

• Should each and every table have a primary key?

Answer 2

Short answer: yes.

Longer answer: if you have a table to be used in a many-to-many relationship, you don't really need a primary key. Then it can be regarded as a waste of space. There might be more examples, this is just one proof for the answer "yes" :-)

Answer 3

In my experience, almost never. (For a "speed matters, I'm just inserting and don't really care about retrieval at this point" style of application, perhaps.)

Whilst you might conceivably never use the ID field, it's nearly always wise to have one happily AUTO_INCREMENTing away, because one day you might need one. (You could of course simply do an 'ALTER..' to add one, but that's besides the point.)