A Little Noise

April 9, 2009

errno: 121 (Duplicate key) with CREATE TABLE

Filed under: MySQL FAQ — snoyes @ 7:02 am

Trying to create a table, and getting something like this?

ERROR 1005 (HY000): Can't create table '<db>.<table>' (errno: 121)

Discovered that perror 121 says this?

MySQL error code 121: Duplicate key on write or update

Really confused how you might get a duplicate key error while creating a table?

If the table you’re trying to create includes a foreign key constraint, and you’ve provided your own name for that constraint, remember that it must be unique within the database. Run this query to see if that name is in use somewhere:

SELECT 
  constraint_name,
  table_name
FROM 
  information_schema.table_constraints 
WHERE 
  constraint_type = 'FOREIGN KEY' 
  AND table_schema = DATABASE() 
ORDER BY 
  constraint_name;

(If you’re still on 4.1 or earlier,

mysqldump --no-data yourDbName | grep CONSTRAINT

to get a similar list)

Thanks to [raymond] on Freenode.

17 Comments »

  1. In my case, it was an ALTER TABLE … ADD CONSTRAINT query and the name was in use by an index.

    Comment by Álvaro G. Vicario — April 27, 2010 @ 11:20 am

  2. Thank you very much. Worked like a charm.

    Comment by James — August 11, 2010 @ 12:48 pm

  3. Thanks a lot!

    Comment by Artem — August 23, 2010 @ 10:36 am

  4. Great !! It worked.

    Comment by Senthil — September 4, 2010 @ 7:25 am

  5. Thank you so much!!!! It worked

    Comment by DUSMANTA SAHOO — November 20, 2012 @ 1:35 am

  6. Thanks for post solution for ErrorNo:121, now my DB successfully executed, once again thanks to u…

    Comment by Chandrababu — December 1, 2012 @ 2:51 am

  7. Thank you very much. I have been tried so many things last two hours and I have fixed errors thanks to your note.

    Comment by Furkan Çakmak — December 4, 2012 @ 5:30 am

  8. Amazing. Thank you! From San Diego, Josh

    Comment by Josh — December 13, 2012 @ 9:34 pm

  9. Awesomeness your way. Thanks!

    Comment by Per Olesen — January 6, 2013 @ 8:15 am

  10. Me and my professor have been searching for 2 weeks on how to fix this problem and finally someone pulled through!

    -Thanks!!

    Comment by Ashton — April 1, 2013 @ 6:48 pm

  11. You sir just saved my day. Thank you!

    Comment by me — April 12, 2013 @ 2:08 am

  12. […] to this post you can check your constraint like […]

    Pingback by Error creating foreign key from MySQL Workbench | CL-UAT — December 27, 2014 @ 2:48 am

  13. Worked

    Comment by Visha — February 5, 2015 @ 5:19 am

  14. […] to this post you can check your constraint like […]

    Pingback by Error creating foreign key from MySQL Workbench | XL-UAT — February 28, 2015 @ 4:36 pm

  15. Thank you Sir!

    Comment by Waqas — April 20, 2015 @ 12:12 am

  16. i think its poor that mySQL does not give a hint or a better error message regarding this.

    Comment by cyrene — February 2, 2016 @ 3:51 pm

  17. […] ?? ???? ????? ? ???? ?????: http://thenoyes.com/littlenoise/?p=81 ???? ???????, ?? ??? ????? ?? ?????????? ?????, […]

    Pingback by MySQL — Can’t create table (errno: 121) — Small Coder Blog — March 7, 2016 @ 10:38 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress