Error 1022 - Can t write duplicate key in table

Question

I m getting a 1022 error regarding duplicate keys on create table command  Having looked at the query  I can t understand where the duplication is taking place  Can anyone else see it    SQL query   -- ----------------------------------------------------- -- Table  apptwo   usercircle  -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS   apptwo   usercircle       idUserCircle  MEDIUMINT NOT NULL     userId  MEDIUMINT NULL     circleId  MEDIUMINT NULL     authUser  BINARY NULL     authOwner  BINARY NULL     startDate  DATETIME NULL     endDate  DATETIME NULL   PRIMARY KEY     idUserCircle      INDEX   iduser idx      userId  ASC     INDEX   idcategory idx      circleId  ASC     CONSTRAINT   iduser  FOREIGN KEY     userId    REFERENCES   apptwo   user     idUser    ON DELETE NO ACTION ON UPDATE NO ACTION   CONSTRAINT   idcategory  FOREIGN KEY     circleId    REFERENCES   apptwo   circle     idCircle    ON DELETE NO ACTION ON UPDATE NO ACTION   ENGINE   INNODB   MySQL said  Documentation   1022 - Can t write  duplicate key in table  usercircle

User · Answer

I just spent the last 4 hours with the same issue. What I did was to simply make sure the constraints had unique names.

You can rename the constraints. I appended a number to mine so I could easily trace the number of occurrences.

Example

If a constraint in a table is named boy with a foreign key X The next constraint with the foreign key X can be called boy1

I'm sure you'd figure out better names than I did.

User · Answer

I had this problem when creating a new table   It turns out the Foreign Key name I gave was already in use   Renaming the key fixed it

User · Answer

You are probably trying to create a foreign key in some table which exists with the same name in previously existing tables  Use the following format to name your foreign key tablename columnname fk

User · Answer

I also encountered that problem Check if database name already exist in Mysql and rename the old one

User · Answer

As others have mentioned  it s possible that the name for your constraint is already in use by another table in your DB  They must be unique across the database   A good convention for naming foreign key constraints is   fk TableName ColumnName   To investigate whether there s a possible clash  you can list all constraints used by your database with this query   SELECT   FROM information schema table constraints WHERE constraint schema    YOUR DB     When I ran this query  I discovered I had previously made a temporary copy of a table and this copy was already using the constraint name I was attempting to use

User · Answer

This can also arise in connection with a bug in certain versions of Percona Toolkit s online-schema-change tool   To mutate a large table  pt-osc first creates a duplicate table and copies all the records into it   Under some circumstances  some versions of pt-osc 2 2 x will try to give the constraints on the new table the same names as the constraints on the old table   A fix was released in 2 3 0   See https   bugs launchpad net percona-toolkit  bug 1498128 for more details

User · Answer

The most likely you already have a constraint with the name iduser or idcategory in your database  Just rename the constraints if so   Constraints must be unique for the entire database  not just for the specific table you are creating altering   To find out where the constraints are currently in use you can use the following query   SELECT  TABLE SCHEMA    TABLE NAME  FROM  information schema   KEY COLUMN USAGE  WHERE  CONSTRAINT NAME  IN   iduser    idcategory

User · Answer

Change the Foreign key name in MySQL  You can not have the same foreign key names in the database tables    Check all your tables and all your foreign keys and avoid having two foreign keys with the same exact name

User · Answer

From the two linksResolved Successfully and Naming Convention  I easily solved this same problem which I faced  i e   for the foreign key name  give as fk colName TableName  This naming convention is non-ambiguous and also makes every ForeignKey in your DB Model unique and you will never get this error      Error 1022  Can t write  duplicate key in table

[mysql] Error 1022 - Can't write; duplicate key in table

Examples related to mysql