A Little Noise


errno: 121 (Duplicate key) with CREATE TABLE

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:

  constraint_type = 'FOREIGN KEY' 
  AND table_schema = DATABASE() 

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

Filed under: MySQL FAQ Leave a comment
Comments (14) Trackbacks (3)
  1. In my case, it was an ALTER TABLE … ADD CONSTRAINT query and the name was in use by an index.

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

  3. Thanks a lot!

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

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

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

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

  8. Awesomeness your way. Thanks!

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


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

  11. Thank you Sir!

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

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.