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?

Provincetown

Answer 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.

Jedidiah

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. – Chaussure

Answer 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.

Avenue

Answer 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.

Suction

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. – Stores

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

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. – Vetter

Answer 4

This is kind of a response to @peter-moses, as I had exactly the same issues as he did.

I solved that problem by adding --add-drop-database to my dump command. Its documented here: https://mariadb.com/kb/en/mysqldump/

My final command looks like this:

mysqldump -uroot -ppassword --single-transaction --all-databases --add-drop-database

Trovillion

Answer 5

In my case it was very strange caching issue. I was not able to create a new foreign key constraint, although the name of the key was unique. Restarting MariaDB server was not the solution, but checking both Foreign Keys and References lists.

For example, in my Database navigator (I am using DBeaver) all my Foreign Keys lists were empty. However, when I navigated to the referenced table and checked its References list, I was able to see the entries. By the way, these entries were also not visible when executing a query against information_schema. 🤦‍♂️

After removing the references from this list I was able to create new foreign keys.

Edit

Executing SHOW ENGINE INNODB STATUS; and checking its LATEST FOREIGN KEY ERROR section might also help, as it gives additional information/context for the problem.

Interested

I'm using DBeaver 22.3.1, and "Foreign Keys" node doesn't show any existing fkeys, which is a system bug, I believe. In fact, foreign key was there and was able to verify it using on DOS (not in DBeaver script)-- select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_SCHEMA = 'mydb' AND CONSTRAINT_TYPE = 'FOREIGN KEY'; – Zeculon

Answer 6

I'm using DBeaver22.3.1 and suffered the same issue as below: That is, [Foreign keys] node didn't show any item on it(see below fig.)

However, using following select statement, it's existance was obvious(see 2nd image). Therefore, error says "duplicate" if I try to create a foreign key with the same name.

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
where CONSTRAINT_SCHEMA = 'webstore' 
        AND CONSTRAINT_TYPE = 'FOREIGN KEY';

Zeculon