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

It depends on your data design and the way in which you access data in the table.

A log table usually doesn't need a primary key, because you often access logs in groups and don't delete/edit single messages (you may purge the whole table, or a time window of logs).

Other times, an ID field could be redundant. If customers subscribe to your website using OpenID (or simply an email address) then you already have your primary key. However, in this case, it's a good practice to add the redundant ID field, because an integer uses less space than strings, and you are supposed to repeat the ID in all relationships the entity is involved in!

Finally, relationship tables don't need an explicit ID in 99% of cases. Example: a many-to-many relationship between Users and Groups. The relationship table only consists in user ID and group ID, and that is your primary key (you would like to index the two fields separately for performance...).

By the way, the auto-incrementing ID doesn't slow performance down significantly. The counter value is stored in the table's metadata, so when the DBMS performs an insert it automatically increments the reference counter, and this can be done with the equivalent Java's AtomicInteger and C#'s Interlocked, so you don't have to worry about it at all!

Answer 2

For databases of first normal form or second normal form.

Answer 3

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 4

For performance/space, I avoid autonumber ID fields if possible. If you have a very large table (many millions or billions of records) then space is important. if you can find a good, usable primary key based on other field (or fields) then you are better off using that than introducing an ID field.

No sense in having an auto-number primary key if you can use a set of fields that you have a unique index as a primary key anyway. You just need to be careful with UPDATEs to maintain referential integrity.

Answer 5

Having a primary key is a good idea (and necessary if you want to have a fully normalized database design).

Personally, if the table has a natural candidate key I will use that most of the time, rather than adding an ID column that has to be artificially populated.

Answer 6

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 7

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.)