2

If I understand this correct, AUTO_INCREMENT= is only helpful for tables which are self growing, like a user log table, user details table, etc need to insert/delete records dynamically. But for lookup tables like cities, languages, etc which I manually need to inset/delete data there is no benefit correct, esp since I have to create the FK for these in other tables so if i manually create the PK ID then i can match it properly as FK in other tables vs alowing the system to crate auto create PK and then i have to find it and use those in other tables as FK?

Mia
  • 23
  • 2

3 Answers3

1

Every table should have a primary key. So setting the primary key with AUTO_INCREMENT is always useful.

Community
  • 1
  • 1
unutbu
  • 842,883
  • 184
  • 1,785
  • 1,677
  • 1
    Every table should have a primary key, I entirely agree, but that doesn't mean every table should have an auto incremented primary key. – Vincent Savard Nov 06 '10 at 00:03
  • That is my point too, what is benefit for having vs not having auto incremented primary key? And if having then I have seen different values used. On my system they use 1, 6 and 10 without much though into why and how these numbers came about. – Mia Nov 06 '10 at 00:52
  • Why a particular increment? That depends on implementation... Have four sites whose tables are UNIONed together for reporting? +4 on each and staggered start points (ensures no two have duplicate key values). Intend to INSERT related rows in some round of elaboration? +10 (for example) to leave enough room for the related rows. (Not a great implementation, but I've seen it done.) – Eric Towers Nov 06 '10 at 07:30
1

Using an auto_increment as the PK in a cities table ensures that subsequent inserts and deletes do not cause foreign references to suddenly reference the wrong thing. (I.e. prevents inadvertent reuse of a key value).

Eric Towers
  • 4,175
  • 1
  • 15
  • 17
1

I don't believe that it's mandatory to have an auto_increment column. Primary key must be unique, and you should have some column (or set of columns) with a primary key constraint in every table. But using an automatic pseudokey is not required.

The advantage of auto_increment is that it guarantees no two inserts will generate the same number, even if they're running in concurrent transactions. You can't do this manually without locking, which effectively queues up your clients and hurts throughput.

I wrote a chapter about this issue in my book SQL Antipatterns.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828