MySQL duplicate key error

Question

If I run this code all work well, but if uncomment last constraint, I got the following error:

Error Code: 1022. Can't write; duplicate key in table 'transfer'

but there no another key 'fk_component_id', what wrong with this code?

-- -----------------------------------------------------
-- Table `pcdb`.`transfer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pcdb`.`transfer` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `component_id` INT UNSIGNED NOT NULL,
  `type` INT NULL,
  `parent_id` INT UNSIGNED NULL,
  `source_id` INT UNSIGNED NULL,
  `contractor_id` INT UNSIGNED NULL,
  `src_department_id` INT UNSIGNED NULL,
  `dest_department_id` INT UNSIGNED NULL,
  `session_id` INT UNSIGNED NOT NULL,
  `count` INT UNSIGNED NOT NULL,
  `comment` TEXT(65535) NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_src_department_id`
    FOREIGN KEY (`src_department_id`)
    REFERENCES `pcdb`.`department` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_income_id`
    FOREIGN KEY (`source_id`)
    REFERENCES `pcdb`.`transfer` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION, 
  CONSTRAINT `fk_contractor_id`
    FOREIGN KEY (`contractor_id`)
    REFERENCES `pcdb`.`contractor` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_session_id`
    FOREIGN KEY (`session_id`)
    REFERENCES `pcdb`.`session` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_dest_department_id`
    FOREIGN KEY (`dest_department_id`)
    REFERENCES `pcdb`.`department` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_parent_id`
    FOREIGN KEY (`parent_id`)
    REFERENCES `pcdb`.`transfer` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION/*, 
  CONSTRAINT `fk_component_id`
    FOREIGN KEY (`component_id`)
    REFERENCES `pcdb`.`component` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION*/);

-- ----------------------------------------------------- -- Table pcdb.component -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS pcdb.component ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(255) NULL, PRIMARY KEY (id)); — user2075764
– user2075764, Commented May 3, 2015 at 6:17

John Hodge · Accepted Answer · 2015-05-03 06:18:41Z

2

It sounds like you may already have a constraint with that name on a different table in the database. Try changing the name of that constraint to something like "fk_transfer_component_id" and see if you still get the error.

answered May 3, 2015 at 6:18

John Hodge

1,7151 gold badge15 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2075764 Over a year ago

yes, it helps, I just think foreign key should be unique per table, but it seems that it should be unique in the entire scheme

Collectives™ on Stack Overflow

MySQL duplicate key error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related