KP Solutions

Solutions for Day to Day Technical Problems

MySql Error#1005 – Can’t create table (errno: 121)

May 5, 2011 by Ketan Patel | 16 Comments

I have been MySql Workbench for a long time now and I recommend everyone to use it for their database design needs. Today, I was exporting the mysql database design to .sql file and then importing the sql to mysql server. I got the error

"#1005 - Can't create table 'XXX.xxxx' (errno: 121)". .

I scratched my head, hmmm…. Error #1005 is for foreign key constraint errors, checked the keys, column type and everything looked OK.

After about 10 minutes of fiddling around, I found that I had forgotten to make the foreign key constraint names unique for that particular table. If you are using InnoDB engine, then you need to make sure that all the foreign key constraint names are unique across the entire database and not just the table.

Hopefully, you may find my post helpful and save you some minutes or hours sometimes.

Cheers,
Ketan

  • gluca

    You are my hero.
    That saved a day of searching google :).

  • Hungrymind Bb

    Thank you very much, But some thing hard to understandable concept of MYSql!…

  • http://twitter.com/elemoll Eleazar Moll

    thank you “amigo”, then 10 minutes i found your page… 🙂

  • Fixticks

    Mine was an actual foreign key constraint error but yo article gave me lotsa insight. Thanks

  • Akrt130

    you are best my man love from turkey  :)) good lecture

  • Kinetic_one

    Thanks Mate,
    you saved me time!

  • Topiq

    Thanks! 🙂

  • Mihaly Hanol

    As everybody says, you’re a hero!

    THX

  • sent

    awesome!!!!!!!!!!!!!!!! 

  • Captain Lame-O

    “you need to make sure that all the foreign key constraint names are unique across the entire database and not just the table.”

    What does that mean?

  • kpsolution

     It means that in a database, you may have 10 tables. So a given foreign key constraint name should be used only once in a database and not repeated again in any of the other tables.

  • Thomas

    I’m one of the many whom you saved a lot of time and pain. Thanks a lot. 

  • Gorgan11

    dude you are the best!

  • kpsolution

     Thanks buddy!

  • sixthfore

    Thanks! Was running into a deluge of errors — this helps a lot!

  • Mayur Jadeja

    Thanks! it help me lot…