6

Possible Duplicate:
MySQL: Reorder/Reset auto increment primary key?

if I delete from my table a ligne and in this table i have an auto increment ID , DELETE From table where id=2

after this the table will be like this 

 ID   | NAme
1        aaa
3        cccc

how can i fix the ID to be like this 

ID   | NAme
1        aaa
2        cccc
3        dddd
Community
  • 1
  • 1
Dilllllo
  • 448
  • 5
  • 10
  • 24
  • I am not sure why you want to do this, ID is auto incremented for a reason. – ChrisBint Jun 26 '11 at 19:31
  • 2
    Most certainly, you wouldn't want to do that. It could lead to dangerous confusions... – blubb Jun 26 '11 at 19:31
  • +1 for the really good question – Benny Tjia Jun 26 '11 at 19:32
  • This has a serious drawback, you or the DBMS has to reorganize all records after the deleted record. Not only that, if ID is your primary key, all dependant tables also need to be updated (in order to keep referential integrity). In general [primary keys should never change](http://stackoverflow.com/questions/337503/whats-the-best-practice-for-primary-keys-in-tables/338424#338424). – Christian Ammer Jun 26 '11 at 19:53

2 Answers2

2

Look at this answer: Reorder / reset auto increment primary key

Community
  • 1
  • 1
Sebastiano Merlino
  • 1,273
  • 12
  • 23
0

Autoincrement doesn't work this way. If you have 2 records on table with ID-s 1 and 2 and you delete the record with ID 2, then the next will always will be still 3.

Suggest you to add additional column with holding those values for the reasons you need it for.

Also, I learned in school long time ago, that in great systems, never records are deleted. There's a column holding timestamp value that marks them as "deleted".

evilone
  • 22,410
  • 7
  • 80
  • 107