0

I have a table with name as listings and inside there I have a COLUMN namely as when some rows are deleted so the AUTO Incrmementation columns namely as "ID" goes into soemthing very bad values..Like missing values in between which I don't like and don't suit like a professional way..so therefore I want please if you people can guide me to how reset all ID columns values in rows on each INSERT or DELETE Query Exeution please..!

user4089052
  • 43
  • 1
  • 6
  • This is not recommended, cause we use ID as field value of other tables and this can be complicated. All new values should be greater than the previous ones, this is the professional way. This is normal that some values are missing, you could deal with that, if you have some trouble, post it on stackoverflow, we will help you. ;-) – Loenix Oct 07 '14 at 10:48
  • 1
    The purpose of an `AUTO_INCREMENT` column is to supply unique values _without any maintenance_. The sequence doesn't need to be sequential or unbroken. That is how it is supposed to be. You shouldn't change primary key values just to make the numbers "neater". – Turophile Oct 07 '14 at 10:49

1 Answers1

2

If you really want to find the lowest unused key value, don't use AUTO_INCREMENT at all, and manage your keys manually. However, this is NOT a recommended practice.

AS explained at Auto Increment after delete in MySQL

Primary autoincrement keys in database are used to uniquely identify a given row and shouldn't be given any business meaning. So leave the primary key as is and add another column called for example courseOrder. Then when you delete a record from the database you may want to send an additional UPDATE statement in order to decrement the courseOrder column of all rows that have courseOrder greater than the one you are currently deleting.

As a side note you should never modify the value of a primary key in a relational database because there could be other tables that reference it as a foreign key and modifying it might violate referential constraints.

Well it is not recommended but you insisted , so use this to re order By using something like:

ALTER TABLE table_name AUTO_INCREMENT = 1;
Community
  • 1
  • 1
Tushar Gupta
  • 15,504
  • 1
  • 29
  • 47
  • well...now it makes some sense..but please can you provide me with the manual commands for decrementation which I will be using then after a record is being deleted from between..! so then just records onwards that record need to be decremented not all from start right..? – user4089052 Oct 07 '14 at 10:57