J. Doe J. Doe - 1 month ago 8
MySQL Question

MySQL Workbench Create SQL Statements error when implementing in phpMyAdmin

I Created a Whole Database in MySQL Workbench and now im trying to import it to my DB on my Webhost through phpMyAdmin. The Workbench created following code:

CREATE TABLE IF NOT EXISTS `usr_web375_4`.`Sandwich` (
`Sandwich_ID` INT NOT NULL,
`Style_ID` INT NOT NULL,
`Bread_ID` INT NOT NULL,
`Cheese_ID` INT NULL,
`Size_ID` INT NOT NULL,
`Sandwich_Toasted` TINYINT(1) NOT NULL,
`Sandwich_Prize` FLOAT NOT NULL,
PRIMARY KEY (`Sandwich_ID`),
INDEX `Style_ID_idx` (`Style_ID` ASC),
INDEX `Bread_ID_idx` (`Bread_ID` ASC),
INDEX `Cheese_ID_idx` (`Cheese_ID` ASC),
INDEX `Size_ID_idx` (`Size_ID` ASC),
CONSTRAINT `Style_ID`
FOREIGN KEY (`Style_ID`)
REFERENCES `usr_web375_4`.`Style` (`Style_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Bread_ID`
FOREIGN KEY (`Bread_ID`)
REFERENCES `usr_web375_4`.`Bread` (`Bread_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Cheese_ID`
FOREIGN KEY (`Cheese_ID`)
REFERENCES `usr_web375_4`.`Cheese` (`Cheese_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Size_ID`
FOREIGN KEY (`Size_ID`)
REFERENCES `usr_web375_4`.`Size` (`Size_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `usr_web375_4`.`Sauce` (
`Sauce_ID` INT NOT NULL,
`Sauce_Name` VARCHAR(45) NULL,
`Sauce_IMG` VARCHAR(45) NULL,
`Sauce_Nutrition` INT NULL,
PRIMARY KEY (`Sauce_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `usr_web375_4`.`SandwichSauce`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `usr_web375_4`.`SandwichSauce` (
`SandwichSauce_ID` INT NOT NULL,
`Sandwich_ID` INT NOT NULL,
`Sauce_ID` INT NOT NULL,
PRIMARY KEY (`SandwichSauce_ID`),
INDEX `Sandwich_ID_idx` (`Sandwich_ID` ASC),
INDEX `Sauce_ID_idx` (`Sauce_ID` ASC),
CONSTRAINT `Sandwich_ID`
FOREIGN KEY (`Sandwich_ID`)
REFERENCES `usr_web375_4`.`Sandwich` (`Sandwich_ID`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `Sauce_ID`
FOREIGN KEY (`Sauce_ID`)
REFERENCES `usr_web375_4`.`Sauce` (`Sauce_ID`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;


i tried it either with the import function and the code section.
It gives me following error:

CREATE TABLE IF NOT EXISTS `usr_web375_4`.`SandwichSauce` (


SandwichSauce_ID
INT NOT NULL,
Sandwich_ID
INT NOT NULL,
Sauce_ID
INT NOT NULL,
PRIMARY KEY (
SandwichSauce_ID
),
INDEX
Sandwich_ID_idx
(
Sandwich_ID
ASC),
INDEX
Sauce_ID_idx
(
Sauce_ID
ASC),
CONSTRAINT
Sandwich_ID

FOREIGN KEY (
Sandwich_ID
)

#1005 - Can't create table 'usr_web375_4.SandwichSauce' (errno: 121) (Details…)


I don't get it. there was no error in workbench

I'm not sure if it has maybe something to do with this table i successfully created:

CREATE TABLE IF NOT EXISTS `usr_web375_4`.`SandwichVegtable` (
`SandwichVegtable_ID` INT NOT NULL,
`Sandwich_ID` INT NOT NULL,
`Vegtable_ID` INT NOT NULL,
PRIMARY KEY (`SandwichVegtable_ID`),
INDEX `Sandwich_ID_idx` (`Sandwich_ID` ASC),
INDEX `Vegtable_ID_idx` (`Vegtable_ID` ASC),
CONSTRAINT `Sandwich_ID`
FOREIGN KEY (`Sandwich_ID`)
REFERENCES `usr_web375_4`.`Sandwich` (`Sandwich_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Vegtable_ID`
FOREIGN KEY (`Vegtable_ID`)
REFERENCES `usr_web375_4`.`Vegtable` (`Vegtable_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB

Answer

You must add a type for Sandwich_ID and Sauce_ID in your CREATE TABLE IF NOT EXISTSusr_web375_4.SandwichSauce ...

and you must use a unique names for all your indexes and constraints.

Could you try this code :

CREATE TABLE IF NOT EXISTS `usr_web375_4`.`SandwichSauce` (
  `SandwichSauce_ID` INT NOT NULL,
  `Sandwich_ID` INT NOT NULL,
  `Sauce_ID` INT NOT NULL,
  PRIMARY KEY (`SandwichSauce_ID`),
  INDEX `Sandwich_ID_idx` (`Sandwich_ID` ASC),
  INDEX `Sauce_ID_idx` (`Sauce_ID` ASC),
  CONSTRAINT `Sandwich_ID_1`
    FOREIGN KEY (`Sandwich_ID`)
    REFERENCES `usr_web375_4`.`Sandwich` (`Sandwich_ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `Sauce_ID`
    FOREIGN KEY (`Sauce_ID`)
    REFERENCES `usr_web375_4`.`Sauce` (`Sauce_ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB

Or in workbench you can run this query and export the DB again :

 ALTER TABLE `SandwichSauce` DROP FOREIGN KEY `Sandwich_ID`;
 ALTER TABLE `SandwichSauce` ADD CONSTRAINT `Sandwich_ID_1` FOREIGN KEY (`Sandwich_ID`) REFERENCES `usr_web375_4`.`Sandwich`(`Sandwich_ID`) ON DELETE CASCADE ON UPDATE CASCADE;
Comments