Errno 121, duplicate key on write or update?

Question

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`restaurants`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`restaurants` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(128) NOT NULL ,
  `description` VARCHAR(1024) NOT NULL ,
  `address` VARCHAR(1024) NOT NULL ,
  `phone` VARCHAR(16) NOT NULL ,
  `url` VARCHAR(128) NOT NULL ,
  `min_order` INT NOT NULL ,
  `food_types` SET('pizza', 'sushi', 'osetian_pie') NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `name_UNIQUE` (`name` ASC) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`regions`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`regions` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `restaurant` INT NOT NULL ,
  `name` VARCHAR(128) NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `restaurant_idx` (`restaurant` ASC) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  CONSTRAINT `restaurant`
    FOREIGN KEY (`restaurant` )
    REFERENCES `mydb`.`restaurants` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`food`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`food` (
  `id` INT NOT NULL ,
  `type` ENUM('pizza', 'sushi', 'osetian_pie') NOT NULL ,
  `name` VARCHAR(45) NOT NULL ,
  `ingredients` VARCHAR(256) NULL ,
  `image` VARCHAR(256) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`food_variant`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`food_variant` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `size` VARCHAR(16) NOT NULL ,
  `weight` VARCHAR(16) NOT NULL ,
  `price` INT NOT NULL ,
  `food` INT NOT NULL ,
  `restaurant` INT NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  INDEX `food_idx` (`food` ASC) ,
  INDEX `restaurant_idx` (`restaurant` ASC) ,
  CONSTRAINT `food`
    FOREIGN KEY (`food` )
    REFERENCES `mydb`.`food` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `restaurant`
    FOREIGN KEY (`restaurant` )
    REFERENCES `mydb`.`restaurants` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Error is:
    Executing SQL script in server
    ERROR: Error 1005: Can't create table 'mydb.food_variant' (errno: 121)

I see no duplicate constraints. Where is it?

Solution 1

This is likely because you have named at least one constraint with the same identifier as a column:

/* You already have a column named `restaurant` in this table, 
   but are naming the FK CONSTRAINT `restaurant` also... */
CONSTRAINT `restaurant`
    FOREIGN KEY (`restaurant` )
    REFERENCES `mydb`.`restaurants` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

Should use a different identifier for the constraint like fk_restaurant as in :

CONSTRAINT `fk_restaurant`
    FOREIGN KEY (`restaurant` )
    REFERENCES `mydb`.`restaurants` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

And same thing in the food table:

  /* Name it fk_food */
  CONSTRAINT `fk_food`
    FOREIGN KEY (`food` )
    REFERENCES `mydb`.`food` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  /* Name it fk_restaurant */
  CONSTRAINT `fk_restaurant`
    FOREIGN KEY (`restaurant` )
    REFERENCES `mydb`.`restaurants` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

Those are the only three I see, but there could be others I missed.

Solution 2

This answer comes from the comment to the answer from @Michael Berkowski. I post it as an answer as it actually worked for me:

I was getting the errno 121 even after changing the constraint names across multiple tables. The problem was that even across different tables you cannot have the same constraint name. I was using fk_entryid in table1 and table2 and had to change them to fk_table1_entryid and fk_table2_entryid respectively to make it work.

Solution 3

All the answers above are superb but didn't solve my problem even after i dropped all my tables, but everything worked perfectly and the migration ran smoothly after i dropped my DB and created it again.... It seems the keys are cached and aren't cleared after tables are dropped.

Note : This is not an answer to the question but my experience that i felt might help another person.

Comments

[Jose Cifuentes]

In case someone hits this problem. I was getting the errno 121 even after changing the constraint names across multiple tables. The problem was that even across different tables you cannot have the same constraint name. I was using 'fk_entryid' in table1 and table2 and had to change them to 'fk_table1_entryid' and 'fk_table2_entryid' respectively to make it work. This happened with MySQLWorkbench and MariaDB in case that matters.

[bmakan]

This was the issue for me as well. I had to drop the database completely. My guess is something got messed up some time ago when I had issues stopping mysql daemon and had to kill it forcefully.

[Yousef Altaf]

same with me. just dropped the database and re-import it solved the issue

[twigmac]

We had the same issue. We were able to solve it by stopping and restarting the MySQL/MariaDB server. Hence, dropping the database might be unnecessary.