# mysql – Error 1022 – Can’t write; duplicate key in table

## The Question :

228 people think this question is useful

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'


• If I remember correctly, the primary key is always also a UNIQUE INDEX, so you would have to drop the unique index statement?
• ON DELETE NO ACTION would just drop the entire use of the foreign key. Unless you have very specific reasons to do that.
• @AmazingDreams Why? It still enforces referential integrity. Only you have to delete the children yourself. This is safer than a cascaded delete where you could accidentally delete lots of data by deleting one incorrect keyword.
• stackoverflow.com/a/5810024/1567737 Why use an alias when using the ‘aliased’ makes the purpose clear immediately?
• @AmazingDreams Thanks for the tip. I like the debate around it as well – it helps me to learn about the pros and cons.

559 people think this answer is useful

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');



31 people think this answer is useful

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.

15 people think this answer is useful

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

6 people think this answer is useful

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.

4 people think this answer is useful

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

3 people think this answer is useful

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.

1 people think this answer is useful

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

1 people think this answer is useful

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.

tablename_columnname_fk