MySQL: Duplicate Key Error 1022 for non duplicate key

Question

I'm trying to create a table with different foreign key constraints but mysql complains about this DDL:

CREATE TABLE tasks (
        id INTEGER NOT NULL AUTO_INCREMENT, 
        `createdAt` DATETIME NOT NULL DEFAULT now(), 
        `updatedAt` DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(), 
        `createdBy_user_id` INTEGER NOT NULL, 
        `updatedBy_user_id` INTEGER NOT NULL, 
        status ENUM('open','closed') NOT NULL, 
        customer_id INTEGER NOT NULL, 
        `projectDescription` TEXT, 
        PRIMARY KEY (id), 
        CONSTRAINT user_id_fk FOREIGN KEY(`createdBy_user_id`) REFERENCES users (id), 
        CONSTRAINT customer_id_fk FOREIGN KEY(customer_id) REFERENCES `Customer` (id), 
        CONSTRAINT user_up_id_fk FOREIGN KEY(`updatedBy_user_id`) REFERENCES users (id)
)COLLATE utf8mb4_german2_ci CHARSET=utf8mb4

I get the error ERROR 1022 (23000): Can't write; duplicate key in table 'tasks' that makes no sense because every constraint has a different name and is applied on different columns.

Strangely MySQL does not complain if i remove the fk-suffix and replace it by something like constr.

Does anybode know why MySQL 5.7 (Server version: 5.7.30-33 Percona Server (GPL), Release 33, Revision 6517692) behaves like this?

I can't [reproduce this problem](https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=4581e77ebedeecbe48c0235686f1d939). Are you sure that this is your exact code? — GMB, Aug 27 '20 at 14:10
Yes. It's generated by a database migration tool and produces this error. Maybe I should mention that I use `Server version: 5.7.30-33 Percona Server (GPL), Release 33, Revision 6517692` — Sebi2020, Aug 27 '20 at 14:11

score 1 · Accepted Answer · answered Aug 27 '20 at 14:17

You have used a constraint with the same name in another table.

This is a way to reproduce easily this problem :

CREATE TABLE foo
(
  id int not null auto_increment primary key
);

CREATE TABLE bar
(
  id int not null auto_increment primary key,
  foo_id int,
  
  CONSTRAINT foo_id_fk FOREIGN KEY(`foo_id`) REFERENCES foo(id) -- <- Check this constraint name
);

CREATE TABLE hello
(
  id int not null auto_increment primary key,
  foo_id int,
  
  CONSTRAINT foo_id_fk FOREIGN KEY(`foo_id`) REFERENCES foo(id) -- <-- same constraint name than before
);

Schema Error: Error: ER_DUP_KEY: Can't write; duplicate key in table 'hello'

To fix this, make sure the FK names are unique. You can ommit the naming of constraints, so MySQL will create one for you.

--        v----------- no name here
CONSTRAINT FOREIGN KEY(`createdBy_user_id`) REFERENCES users (id)

Or you can use a naming convention

Thank you, the error message from the server is really misleading because it lets you think that the problem are two equal keys in the same table. — Sebi2020, Aug 27 '20 at 14:25

MySQL: Duplicate Key Error 1022 for non duplicate key

1 Answers1