I want to set auto increment to 1 by below command, but it doesn't works:
ALTER TABLE tablename AUTO_INCREMENT = 1
I searched and found out that I should use TRUNCATE TABLE. But this command fails due to foreign key constraint. Any idea?
-
What are you actually trying to do? Do you actually want to remove your data?– PhilᵀᴹJul 12, 2016 at 10:28
-
@Phil I want to set auto increment value to 1.– Vahid NajafiJul 12, 2016 at 10:52
Add a comment
|
1 Answer
If you ever need ALTER TABLE tablename AUTO_INCREMENT = 1 you are probably misusing AUTO_INCREMENT. AUTO_INCREMENT is designed to provide unique keys, nothing more. If you need it to be "1", you should invent your own mechanism; do not trust AUTO_INCREMENT.
Would you care to explain your purpose? Perhaps we can help you find a suitable workaround. (No "setting it to 1" is not the purpose, it is your attempted solution to some higher purpose. Step back.)
answered Jul 15, 2016 at 2:34
-
Just because I have done a lot of tests, my id is now a very big number before production level. But anyway not very important. Thanks. Jul 15, 2016 at 15:14
-
Several things can "burn" ids, making then grow faster than desired:
INSERT IGNORE, IODKU,REPLACE, etc. This discusses a way to avoid such during batch normalization. Jul 15, 2016 at 16:53