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:

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.