How to handle fragmentation of auto_increment ID column in MySQL

by

Renumbering will cause confusion. Existing reports will refer to record 99, and yet if the system renumbers it may renumber that record to 98, now all reports (and populated UIs) are wrong. Once you allocate a unique ID it’s got to stay fixed.

Using ID fields for anything other than simple unique numbering is going to be problematic. Having a requirement for “no gaps” is simply inconsistent with the requirement to be able to delete. Perhaps you could mark records as deleted rather than delete them. Then there are truly no gaps. Say you are producing numbered invoices: you would have a zero value cancelled invoice with that number rather than delete it.

More Related Contents:

  1. How to reset AUTO_INCREMENT in MySQL
  2. How to make MySQL table primary key auto increment with some prefix
  3. How to set initial value and auto increment in MySQL?
  4. Get the new record primary key ID from MySQL insert query?
  5. MySQL AUTO_INCREMENT does not ROLLBACK
  6. Get current AUTO_INCREMENT value for any table
  7. SQL: Creating a Relational table with 2 different auto_increment
  8. Prevent auto increment on MySQL duplicate insert
  9. Add Auto-Increment ID to existing table?
  10. How to fill in the “holes” in auto-increment fields?
  11. How to insert data to MySQL with auto-incremented column(field)?
  12. MySQL: #1075 – Incorrect table definition; autoincrement vs another key?
  13. auto_increment by group
  14. How does MySQL Auto Increment work?
  15. Why does MySQL autoincrement increase on failed inserts?
  16. How to force MySQL to take 0 as a valid auto-increment value
  17. Defining Composite Key with Auto Increment in MySQL
  18. mysql Failed to read auto-increment value from storage engine
  19. Too many auto increments with ON DUPLICATE KEY UPDATE
  20. How to reuse auto_increment values? [duplicate]
  21. make an ID in a mysql table auto_increment (after the fact)
  22. Using LIMIT within GROUP BY to get N results per group?
  23. How to change the CHARACTER SET (and COLLATION) throughout a database?
  24. MySQL difference between two rows of a SELECT Statement
  25. Is incrementing a field in MySQL atomic?
  26. Delete from one table with join
  27. MySQL column type “TIMESTAMP” implicitly includes “NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP”
  28. MySQL: how to do row-level security (like Oracle’s Virtual Private Database)?
  29. CREATE command denied to user？
  30. What is the MAX number if I store int(255) in MySQL?

Leave a Comment