Fixing auto increment on MySQL tables when foreign key constrainst fail
I no longer write new content at tenghamn.com, you can find my new web related content at GoPHP.io and my personal blog posts at Ma.rkus.io.
My favorite domain registrar is NameSilo and if you use the affiliate link here it helps me earn some money from my writing: https://www.namesilo.com/pricing.php?rid=ee81e92mn, thanks! If you are new to NameSilo you can use my coupon, MARKUSTENGHAMN to save $1.00.
This post has been migrated and imported into different systems over the years, I have not had a chance to format this post manually so it may be hard to read but I have left it here as a reference.
Please note that you should NOT do this if you don’t know what is wrong. Foreign keys are there for a reason and bypassing them could cause you to lose data and break applications.
Lately I had been dealing with a large mysql import where the export had been handled wrongly and thus caused all of my database tables to lose auto increment on the id field. When trying to add this auto increment back I would get foreign key constraint failed errors.
To get around this error you can turn off foreign key checks while running your query. This will allow you to bypass the restrictions while fixing the database structure. I used the following code to fix all my tables.
SET foreign_key_checks = 0;<br></br>
ALTER TABLE tablename MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT;<br></br>
SET foreign_key_checks = 1;
On the first line we turn off foreign key checks allowing us to make changes to the table freely. On the second line we change our table to use auto increment, replace “tablename” with the name of your table and change “id” to the name of your column. On the third line we turn foreign key checks back on again.