A Little Noise

9Apr/0911

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:

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.

Filed under: MySQL FAQ Leave a comment
Comments (11) Trackbacks (0)
  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!

    -Thanks!!

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


Leave a comment

No trackbacks yet.