0

When I delete a row from my table, the rowID number is deleted; this means that the rowIDs are not truly sorted any more. In this case, I want to reset the rowID such that the new IDs will be sorted and consecutive. I tried to do this with ALTER TABLE :

ALTER TABLE my_table DROP ID;
ALTER TABLE my_table AUTO_INCREMENT = 1;
ALTER TABLE my_table ADD ID int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

... but this doesn't work. How can I get the consecutive numbers?

Prune
  • 76,765
  • 14
  • 60
  • 81
moghaf
  • 43
  • 1
  • 10
  • 1
    Bad idea, what happen if your ID is used as ForeignKey by another table? Do you update that table also? What happen if you leave the 'hole' in the numbering? You can still correctly sort on that column. – Steve Sep 01 '12 at 12:43
  • How would the rowid's not be sorted after `delete`? –  Sep 01 '12 at 13:20

1 Answers1

0

It is technically possible, if somewhat inadvisable - Set start value for AUTOINCREMENT in SQLite . But it sounds like you are using autoincrement wrong, you definitely should not have to change id values of every row each time a row is deleted from the table. This makes joining other tables in a query very difficult for one thing, and will be horrendously slow on a large table. Why does it matter if the id are 1,2,4 and not 1,2,3 anyway ? You can still ORDER BY them the same way.

Community
  • 1
  • 1
Michael Low
  • 24,276
  • 16
  • 82
  • 119
  • id's are not important but I do this just because I want to have number of each row in DataGridView!this is the just solution I found about it!is there any better way to do this?if any please tell me. – moghaf Sep 01 '12 at 13:22