Databases
StackOverflow2013
Postsdbo
POMySQL error 1022 when creating table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL error 1022 when creating table
    primarykey
    data
    text
    <p>MySQL Workbench came up with the following SQL to create a table:</p> <pre><code>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 </code></pre> <p>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.</p> <p>But this one...</p> <pre><code>ERROR 1022 (23000): Can't write; duplicate key in table 'errors_reports' </code></pre> <p>I can't for the life of me see any duplicate keys here. There's only one key defined!</p> <p>I'm running MySQL 5.6 with a fresh default install. There's nothing in the error log.</p> <p>Ideas?</p> <p>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:</p> <pre><code>CONSTRAINT `error_id` FOREIGN KEY (`error_id` ) REFERENCES `mydb`.`errors` (`error_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, </code></pre> <p>which is particularly odd as there is identical code in several other table definitions and those are perfectly okay!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMatt McLeod
    1. USMatt McLeod
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL error 1022 when creating table
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL error 1022 when creating table
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMySQL error 1022 when creating table
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI don't have any problem with this query .. seems like there may be something else going on
      singulars
      1. POMySQL error 1022 when creating table
      1. USExplosion Pills
    2. CONot 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.
      singulars
      1. POMySQL error 1022 when creating table
      1. USKermit
    3. COPulling out the INDEX statements doesn't make a difference.
      singulars
      1. POMySQL error 1022 when creating table
      1. USMatt McLeod
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload