I want to set auto increment to 1 by below command, but it doesn't works:


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 '16 at 10:28
  • @Phil I want to set auto increment value to 1. – Vahid Najafi Jul 12 '16 at 10:52

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.)

| improve this answer | |
  • 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. – Vahid Najafi Jul 15 '16 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. – Rick James Jul 15 '16 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.