0

I have a table in which the first column is auto increment id defined like this INT UNSIGNED PRIMARY KEY AUTO_INCREMENT

Now I want to delete some rows which contains NULL, after that, the id is not consistent, is there any way to make them adjust automatically? Like now I delete the first row, so the id begins from 2 in my table and also I deleted some rows in the middle.

lucapette
  • 20,564
  • 6
  • 65
  • 59
manxing
  • 3,165
  • 12
  • 45
  • 56
  • you can re-indexing by yourself http://stackoverflow.com/questions/6629402/re-indexing-mysql-int-primary-keys-reset-auto-increment/6629447#6629447 – triclosan Nov 30 '11 at 12:22
  • http://stackoverflow.com/questions/1841104/mysql-auto-increment-filling-the-holes – Haim Evgi Nov 30 '11 at 12:23

4 Answers4

12

It's the primary key and it's not supposed to follow the logic you think it does. If you have sequential values 1, 2, 3, 4, 5 and then you delete value with id = 3, you cannot reuse value 3.

Implications are severe, just do not try to force MySQL to reuse values that you have deleted.

If you need some sort of sequencing for "beautiful" display purposes, don't rely on the value of the id column. It's a primary key, it exists only so that each row can be uniquely identified, thus making its actual value meaningless in terms of the outside world.

I know that people usually want their numbers sequential and that gaps look ugly, but if you need to implement that, implement it using some other column that you'll manually update.

Remember, never touch the primary key yourself.

N.B.
  • 13,688
  • 3
  • 45
  • 55
1

generally if you define an auto-incrementing primary key, you do that, to keep your data consistent by ensuring unique and constant id's (per table), so what you want to do, goes against this (pretty common) prerequisite. is this only a cosmetic thing, or why do want to do that?

Marian Theisen
  • 6,100
  • 29
  • 39
0

The way you are using ID column is not supposed to do what you want it to do. Because it should increase everytime a new entry is inserted in the table and according to its working it should not decrease if any value is deleted from it.

You are using it as a primary key and it should be unique overall, so if you will try to change its value to some value earlier assigned, then it is not a good idea.

Suppose first you assign an Id to one client and you mention this id everywhere in your records and then you delete that client from your database and then try to reassign this id to some other client. Then your records for both of the clients will be inter-mixed.

Rest depends on what kind of application you are working upon.

gprathour
  • 14,813
  • 5
  • 66
  • 90
0

Just to add to @N.B., you can always INSERT INTO ... set id=1, ... if you know what your needs and know that ID is deleted (PRIMARY KEY has a unique check). If you need to restore deleted rows from backup, for example.

Moshe L
  • 1,797
  • 14
  • 19