autoincrement problem

[birbal]

i am having some problem with autoincrement field.suppose i have autoincrement field id. where id has value..1,2,3,4,5now if i delete 5 all the field next time when i will insert any row it will start from 6.is there any way to reinitialize that filed that it will start counting from 1.

[justsomeguy]

Why do you need to?

[birbal]

Why do you need to?

cause after i upload my script to web host. i did some insertion to check that all things are working or not. so there is unnescery data now. now i want to start it from 1. is there any way to do that?

[justsomeguy]

It's generally not necessary to do that, but you can use ALTER TABLE to set the value for auto increment.http://dev.mysql.com/doc/refman/5.0/en/exa...-increment.html

[birbal]

it is not working or i think i am missunderstanding the matter. i used ALTER TABLE tbl AUTO_INCREMENT = 1it is affected the rows (showing)but next time i am when inserting something supposetable has two field id primary key auto_increment.insert into t1 values(NULL,"something")it is then starting same as the before. i mean it is starting from the last id rather than 1.

[[dx]]

If you have more tables with, lets say, ID numbers which are auto increment. Than if you start in one table from 1 again, and need some query which uses likeget data from that table where id number is same as in other table.. how will you do that when somewhere id is 1 and somewhere is 6..As justsomeguy says: no need for it.

[boen_robot]

Doing

ALTER TABLE tbl AUTO_INCREMENT = 1

resets the AUTO_INCREMENT counter to whatever the maximum number of the ID column is plus 1, when the specified number is lower than the maximum.This is done to ensure that future increments aren't going to cause an error.If your real problem is keeping the IDs tight regardless of what is being deleted (e.g. 3 in the sequence 1,2,3,4,5), you'll have to do that manually yourself - whenever something is deleted, you'll have to also move the latest record on that newly freed ID (or decrease every ID, but that's going to be much less efficient), and then reset the AUTO_INCREMENT counter to this new maximum (which would be the old maximum minus one).An important part of going this route is to make sure every foreign key of other tables cascades on update. See this article for an example of foreign key cascade.

[birbal]

An important part of going this route is to make sure every foreign key of other tables cascades on update. See this article for an example of foreign key cascade.

that what i was finding. mysql documentetion is littile wired (at least to me). i was finding some practical example of cascade. thank you.

resets the AUTO_INCREMENT counter to whatever the maximum number of the ID column is plus 1, when the specified number is lower than the maximum.

now i got it. why that was happening.

decrease every ID, but that's going to be much less efficient), and then reset the AUTO_INCREMENT counter to this new maximum

is there any way to get the missing id list?

[boen_robot]

Aren't you going to delete items by their ID? You'll know it before the deletion if that's the case - just use that same number.

[birbal]

Aren't you going to delete items by their ID? You'll know it before the deletion if that's the case - just use that same number.

yes it is working now...i forgot that i was going to delete all of the row. i was thinking unescarily. after delete all the row. i did that ALTER. and it is working fine now.thanks all jsg,boen_robot,haris.