0

when i create a new table in mysql(phpmyadmin) in one column like

 id TEXT auto_increment primarykey

so when i began adding items table id column goes 1 2 3 4 5 ..... Okay, no problem so far.

lets say in my table i have id's 1 to 45. and let's say i deleted id's 41, 42, 43, 44 and 45.and after that i add another item to table. id's start from 46 but i want them continue like 41 42 43 .... again.

Is there an options section for that in phpmyadmin?

user3301042
  • 352
  • 2
  • 14
  • Why using "auto_incement" if you don't want it like it is? you may omit "auto_incement" in the creation and use a select max(id) + 1 from ... to obtain the last id for inserting a new item. But what if you delete the the id's 2, 4, 6, 8? – Ice Mar 08 '14 at 22:37
  • 2
    In this case the `id` (identity) column is a *surrogate key*, it should serve *no other purpose* then uniquely identifying a row. If you need it to meet other criteria, you are generally doing something wrong. In some distributed databases, Teradata for example, you have *no idea* what the 'next' value will ever be, and you should never need to, only that the value is unique. Perhaps if you can explain why you *want* all the values to be sequential, we can advise on a different pattern to fulfill your functional requirements. But this is extremely unlikely to be it. – MatBailie Mar 08 '14 at 23:13

3 Answers3

0

No, this isn't how auto_increment works. You can reset the auto_increment pointer in MySQL though, which might give you want you want, depending on your use-cases.

See set auto increment initial value for mysql table

Community
  • 1
  • 1
Daniel
  • 4,481
  • 14
  • 34
0

This is normal, I've seen that in other RDBMSs too.

This is by design, I don't think you can do what you want.

peter.petrov
  • 38,363
  • 16
  • 94
  • 159
0

You can issue a command to reseed the identity field:

alter table mytable auto_increment=41;

however, there really isn't much point in doing this. Numeric keys are just that - they don't need to be contiguous.

slapthelownote
  • 4,249
  • 1
  • 23
  • 27