MySQL error 1022 when creating table

Question

MySQL Workbench came up with the following SQL to create a table:

CREATE  TABLE IF NOT EXISTS `mydb`.`errors_reports` (
   `error_id` INT NOT NULL ,
   `report_short` VARCHAR(15) NOT NULL ,
PRIMARY KEY (`error_id`, `report_short`) ,
INDEX `error_id_idx` (`error_id` ASC) ,
INDEX `report_short_idx` (`report_short` ASC) ,
CONSTRAINT `error_id`
   FOREIGN KEY (`error_id` )
   REFERENCES `mydb`.`errors` (`error_id` )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
CONSTRAINT `report_short`
   FOREIGN KEY (`report_short` )
   REFERENCES `mydb`.`reports` (`report_short` )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB

which looks fine to me, and there are a bunch of other very similar tables in my database which MySQL was perfectly happy to create.

But this one...

ERROR 1022 (23000): Can't write; duplicate key in table 'errors_reports'

I can't for the life of me see any duplicate keys here. There's only one key defined!

I'm running MySQL 5.6 with a fresh default install. There's nothing in the error log.

Ideas?

Edit: through a process of elimination (going back to the simplest possible definition of the table, then gradually adding bits back in) the problem appears to be this bit:

CONSTRAINT `error_id`
    FOREIGN KEY (`error_id` )
    REFERENCES `mydb`.`errors` (`error_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,

which is particularly odd as there is identical code in several other table definitions and those are perfectly okay!

I don't have any problem with this query .. seems like there may be something else going on — Explosion Pills, Feb 22 '13 at 00:12
Not sure that you have to explicitly create an index for the FK columns. I was under the impression that creating the reference would create an index. — Kermit, Feb 22 '13 at 00:25

score 71 · Answer 1 · answered Mar 30 '13 at 21:46

71

The problem is that the name of a foreign key can not be the same as another foreign key in the entire model.

Imagine this situation

Catalog --> Supplier

Product --> Supplier

if the name of the foreign key in table Catalog for supplier is "supplier" and you assigned the same name in product table then the foreign keys names will "collide".

You need to name them differently..

For example:

catalog_supplier product_supplier

answered Mar 30 '13 at 21:46

cSn

2,796
3
23
29

Thanks! Had the same problem and could not have understand that given the error massage. BTW the name of the colliding foreign key is NOT the same as the column name, just an internal MySql name for the key. – uzilan May 18 '13 at 19:28
1

Do you know why that constraint exists in MySQL? – vipw Aug 27 '13 at 09:33

score 2 · Answer 2 · answered Feb 22 '13 at 00:28

2

It seems you're creating an index on the foreign key columns. When creating a foreign key in InnoDb, one will be created automatically.

See this thread.

answered Feb 22 '13 at 00:28

Kermit

33,827
13
85
121

The code was produced by MySQL Workbench, which seems to add that explicitly. Not that it makes any difference. – Matt McLeod Feb 22 '13 at 00:36
@MattMcLeod Are you able to create the constraints after the table is created? Can you also try [checking the indexes](http://stackoverflow.com/a/5213364/679449) before/after the table is created? – Kermit Feb 22 '13 at 00:38
1

Aha! This lead me to what I believe is the solution: another table already had the same constraint applied re: error_id, and Workbench gave both of those the same constraint name. Changing the constraint name allowed it to be created. – Matt McLeod Feb 22 '13 at 00:52
@MattMcLeod Glad to hear you found the root of the problem. It's easier to let MySQL generate the constraint name. – Kermit Feb 22 '13 at 00:53

score -2 · Answer 3 · answered Feb 22 '13 at 00:22

-2

Try using INSERT IGNORE instead of INSERT where INSERT IGNORE will not insert a new row if a duplicate primary key is found. This should help resolve the problem temporary but I would recommend truncating the table.

answered Feb 22 '13 at 00:22

jacek_podwysocki

807
10
30

This is creating the table, not inserting data. There is no INSERT going on here. – Matt McLeod Feb 22 '13 at 00:25
Right.Its definitely the foreign key issue (error 105 for me). Make sure that foreign key and referenced column have the same data types such as collation, default attributes and so on. – jacek_podwysocki Feb 22 '13 at 00:43

MySQL error 1022 when creating table

3 Answers3

Linked

Related