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, 2016 at 10:28
  • @Phil I want to set auto increment value to 1. Jul 12, 2016 at 10:52

1 Answer 1


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

  • 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.
    – Rick James
    Jul 15, 2016 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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