6

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?

Matt
  • 3,778
  • 2
  • 28
  • 32
  • Note that when using InnoDB tables, even if you don't explicitly create a primary key, MySQL will create it's own hidden 6 Byte clustered index. – Mchl Jan 21 '11 at 13:42
  • to Mchl where the reference to the documentation? – Devid G Jan 21 '11 at 16:14

7 Answers7

6

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:

Community
  • 1
  • 1
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • I've had to do couple of queries where having 'id' in my nton tables really helped. – Matt Jan 21 '11 at 14:25
  • @Matt - if it's made things easier, such as it's possible to have the same fact appear multiple times, because you're not enforcing that (AID,BID) values are unique, is it worth it? If you don't enforce the "real" keys, but just have these ID columns, you can easily end up with duplicates in your database. (Conversely, the only benefit I can think of to having an ID column in this case is to allow you to uniquely identify the duplicates when trying to clean up the database) – Damien_The_Unbeliever Jan 21 '11 at 14:39
  • An example case for when its been useful, is joining the table with itself on a foreign key and identifying which result rows are duplicate – Matt Jan 21 '11 at 16:21
  • @Matt: with a composite primary key, you can't have duplicates. Why fighting a problem you can prevent? – Quassnoi Jan 21 '11 at 16:23
  • @Damien: actually the relationship can also serve as an entity and be referenced, in which case it would make sense to make an additional surrogate `id`. This implies that the a deleted and inserted relationship is an instance by itself (which makes it an entity). – Quassnoi Jan 21 '11 at 16:28
4

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" :-)

Dirk
  • 2,167
  • 2
  • 20
  • 29
  • 1
    You need a composite `PRIMARY KEY` in a `many-to-many` relationship table. – Quassnoi Jan 21 '11 at 13:42
  • Not exactly 'need', but it surely makes things run faster. It's also a natural primary key for such table. – Mchl Jan 21 '11 at 13:48
  • Just theoretical: in a many-to-many relationship, all you need is the ID's that need to be linked. You don't really "need" a PK. – Dirk Jan 21 '11 at 13:49
  • @Dirk: in a many-to-many relationship, all related pairs should be only stored once. – Quassnoi Jan 21 '11 at 13:52
  • 2
    @Dirk - `PRIMARY KEY` != `ID`. The fact that so many people conflate the two is why people end up writing confused questions like the above. In a many-many table, you'd define a primary key across both columns, but you wouldn't have an `ID` column. – Damien_The_Unbeliever Jan 21 '11 at 13:55
  • I currently use an 'id' primary key on my NtoN tables and its made things easier – Matt Jan 21 '11 at 14:21
  • 1
    @Matt: I used to do it as well but I dropped it, and it's made things easier. :P – Mchl Jan 21 '11 at 15:26
2

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

John Parker
  • 54,048
  • 11
  • 129
  • 129
2

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.

Dave Costa
  • 47,262
  • 8
  • 56
  • 72
1

For databases of first normal form or second normal form.

Andrew Sledge
  • 10,163
  • 2
  • 29
  • 30
0

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.

Gabriel Magana
  • 4,338
  • 24
  • 23
0

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!

usr-local-ΕΨΗΕΛΩΝ
  • 26,101
  • 30
  • 154
  • 305