• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

MySQL AUTO_INCREMENT column not uniform

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
MySQL database AUTO_INCREMENT (INT type) columns are not increasing uniformly. Ther are increasing like 0,1,2,3,7,8,9,16,17,18...

Why this happens? Will there be any problems of having a non uniform of data column as a Primary Key? How can I have a uniformly AUTO_INCREMENT column?

Thank you.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I would guess it is because the "missing" ids came when an insert operation failed or was aborted? e.g. the auto_increment functionality was triggered then the insert didn't happen.

I don't know how auto_imcrement works in mysql, but it is common for databases to implement primary key generation mechanisms with spaces between the ids. Oracle sequences for example are not uniform, nor are SQL Server identity columns. In both cases, it is the performance trade off gained by grabing an id a "safe" distance from the last one and the overhead required to keep the values contiguous.

That aside, these are surrogate keys and have no business meaning, so what they are does not matter. All that matters is they are unique and unchanging.
reply
    Bookmark Topic Watch Topic
  • New Topic