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'

If I remember correctly, the primary key is always also a UNIQUE INDEX, so you would have to drop the unique index statement? — Mr47, Aug 05 '13 at 11:15
`ON DELETE NO ACTION` would just drop the entire use of the foreign key. Unless you have very specific reasons to do that. — AmazingDreams, Aug 05 '13 at 11:45
@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. — GolezTrol, Aug 05 '13 at 11:52
http://stackoverflow.com/a/5810024/1567737 Why use an alias when using the 'aliased' makes the purpose clear immediately? — AmazingDreams, Aug 05 '13 at 11:59
@AmazingDreams Thanks for the tip. I like the debate around it as well - it helps me to learn about the pros and cons. — Git-able, Aug 05 '13 at 14:40
My problem in Laravel: in my `up` migration, I had defined `$table->foreign('someId')->references('id')->on('users');`, I should *not* have also defined `$table->foreign('someId');`Also, in my `down`, I was using $table->dropForeign('someId'); instead of an array such as $table->dropForeign(['someId']); See https://stackoverflow.com/a/30177480/470749 — Ryan, Jul 01 '18 at 22:31

score 620 · Accepted Answer · edited Jun 07 '18 at 15:51

620

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

edited Jun 07 '18 at 15:51

George

2,860
18
31

answered Aug 05 '13 at 11:54

Maksym Polshcha

18,030
8
52
77

18

Exactly as you said. Many constraints were auto generated with the same idcategory iduser names in the rest of the CREATE query - thanks for your help! – Git-able Aug 05 '13 at 14:38
3

I thought MySQL Workbench would have fixed this when exporting the creation script, but that's what I get for "Ignore"ing the warning about this sort of thing when I opened the project. – SnowInferno Oct 01 '14 at 00:17
Thank you, mate :) That helps me a lot and now my convention for foreign keys is different and I can't met this problem again :) – Dec 06 '15 at 20:45
Can confirm that. But what can I do if the named constraint exists although the referenced table was already dropped?? Can I drop a constraint from a non-existing table? I guess I should update from MySQL 5.6 to current MariaDB. – Anse Apr 10 '18 at 15:24
Whoa, I finally realized that a script of mine had been cloning a table for backup purposes, so *that's* why my database had a duplicate key. – Ryan Jul 05 '18 at 15:35
6

Thank you for this: **Constraints must be unique for the entire database** – sebasira Jan 03 '19 at 16:38
I've also had this problem with MySQL 5.7.26, but being sure I didn't have any other existing key with the same name. There had previously been a key with the same name, but it had been deleted. I solved it by exporting the schema, deleting it and importing it again. I imagine it will be some MySQL problem that does not empty its caches well, despite having restarted the service even, or something similar. – Aníbal May 09 '19 at 12:03
I stumbled across this issue today. The had two takeaways. 1. Constraints must be unique for the entire database. 2. so good convention is, "fk_CurrTableNameColumnName" – Aswin Prasad Mar 08 '20 at 01:36
Try below to see the constraints, SELECT * FROM information_schema.table_constraints WHERE constraint_schema = 'your-db-name'; To delete all, you may have to delete it one by one. Though the below query may help you to form the queries to delete the constraints. SELECT concat('ALTER TABLE ', TABLE_NAME, ' DROP ', CASE WHEN CONSTRAINT_TYPE='PRIMARY KEY' THEN CONSTRAINT_TYPE WHEN CONSTRAINT_TYPE='UNIQUE' THEN CONCAT('INDEX ', CONSTRAINT_NAME) ELSE CONCAT(CONSTRAINT_TYPE, ' ', CONSTRAINT_NAME) END, ';') FROM information_schema.table_constraints WHERE CONSTRAINT_SCHEMA = 'your-db-name'; – NITHIN RAJ T Dec 27 '22 at 03:31

score 34 · Answer 2 · answered Dec 19 '14 at 20:47

34

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.

answered Dec 19 '14 at 20:47

Wassim Sabra

351
3
3

1

That was the problem in my case. I would have never guessed it and you saved my day. Using fk_id_1, fk_id_2 etc, now. Thanks. – JackLeEmmerdeur Dec 22 '16 at 15:22

score 16 · Answer 3 · answered Apr 28 '15 at 06:09

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

score 9 · Answer 4 · answered Jun 04 '18 at 01:49

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.

score 4 · Answer 5 · answered Mar 29 '16 at 16:00

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.

score 4 · Answer 6 · answered Oct 23 '17 at 08:24

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.

score 3 · Answer 7 · answered Jul 27 '20 at 19:34

3

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

answered Jul 27 '20 at 19:34

Shubham Goel

1,962
17
25

1

This is good advice, right to the point. I ended up using the info on this StackOverflow question because I'm working on a system where many of the constraint names didn't follow this kind of pattern. This kind of naming convention, which I was taught and always used, would have prevented this name collision. (I upvoted Chandz response also) – Pete Kelley May 19 '23 at 14:47

score 2 · Answer 8 · answered Feb 08 '18 at 00:33

2

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.

answered Feb 08 '18 at 00:33

user3076750

75
4
12

score 1 · Answer 9 · answered Dec 11 '14 at 02:36

1

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

answered Dec 11 '14 at 02:36

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

9 Answers9

Linked

Related