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
You must add a type for Sandwich_ID and Sauce_ID in your CREATE TABLE IF NOT EXISTS
usr_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;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments