Impossible de créer une table dans Workbench, errno 121

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 = MyISAM;
-- -----------------------------------------------------
-- 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 = MyISAM;
-- -----------------------------------------------------
-- 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 = MyISAM;
-- -----------------------------------------------------
-- 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 = MyISAM;
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)

Je ne vois pas de double contrainte. Où est-il?

source d'informationauteur arts777

  1. 24

    C'est probablement parce que vous avez nommé au moins une contrainte avec le même identifiant comme une colonne:

    /* 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)

    Devez utiliser un identifiant différent pour la contrainte comme fk_restaurant comme dans :

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

    Et même chose dans le 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)

    Ceux sont les trois seuls que je vois, mais il pourrait y en avoir d'autres que j'ai raté.