MySQL Workbench Create SQL Statements error when implementing in phpMyAdmin

J. Doe

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
Imanez

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;

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

MySQL Workbench Error 1005 cant create table

From Dev

MySQL Workbench works and phpmyadmin does not

From Dev

MySQL Workbench works and phpmyadmin does not

From Dev

Create mysql table in phpmyadmin sql window

From Dev

Create mysql table in phpmyadmin sql window

From Dev

MySQL error code: 1064 during CREATE TABLE in MySQL Workbench

From Dev

MySQL error when creating table with phpmyadmin #1016

From Dev

MySQL error when importing database in phpmyadmin

From Dev

Error when creating foreign key of type CHAR with mysql workbench: Error 1005: Can't create table (errno: 150)

From Dev

Error when importing sql database from phpmyadmin

From Dev

Cannot log in to the MySQL server error when logging into phpmyadmin with Google Cloud SQL on Google App Engine

From Dev

MySQL Workbench: TypeError when migrating from SQL Anywhere to MySQL

From Dev

How to open mysql workbench in browser like phpmyadmin?

From Dev

Create ER diagram from sql file in mysql Workbench

From Dev

MySQL Workbench Error 1064

From Dev

Installing MySQL Workbench ⇒ Error

From Dev

MySQL Workbench export error

From Dev

MySql Workbench syntax error

From Dev

Error 1452 SQL WorkBench

From Dev

Preventing SQL statements from getting truncated by MySQL's Workbench in `Performance Reports` section

From Dev

Error 1064 in MySQL Workbench when synchronising a model to a server

From Dev

Parameterize MySQL workbench statements: How to define variables

From Dev

Create function Error in phpmyadmin

From Dev

phpMyAdmin MySQL error # 1045

From Dev

Mysql Error When Changing Column INT to Double through phpmyadmin

From Dev

phpMyAdmin Error - SQL query

From Dev

Error when using export/import SQL file from phpMyAdmin

From Dev

MySQL trigger error 1064 when enclosing if else with multiple statements

From Dev

Rails and MySQL syntax error with multiple SQL statements in an execute block

Related Related

  1. 1

    MySQL Workbench Error 1005 cant create table

  2. 2

    MySQL Workbench works and phpmyadmin does not

  3. 3

    MySQL Workbench works and phpmyadmin does not

  4. 4

    Create mysql table in phpmyadmin sql window

  5. 5

    Create mysql table in phpmyadmin sql window

  6. 6

    MySQL error code: 1064 during CREATE TABLE in MySQL Workbench

  7. 7

    MySQL error when creating table with phpmyadmin #1016

  8. 8

    MySQL error when importing database in phpmyadmin

  9. 9

    Error when creating foreign key of type CHAR with mysql workbench: Error 1005: Can't create table (errno: 150)

  10. 10

    Error when importing sql database from phpmyadmin

  11. 11

    Cannot log in to the MySQL server error when logging into phpmyadmin with Google Cloud SQL on Google App Engine

  12. 12

    MySQL Workbench: TypeError when migrating from SQL Anywhere to MySQL

  13. 13

    How to open mysql workbench in browser like phpmyadmin?

  14. 14

    Create ER diagram from sql file in mysql Workbench

  15. 15

    MySQL Workbench Error 1064

  16. 16

    Installing MySQL Workbench ⇒ Error

  17. 17

    MySQL Workbench export error

  18. 18

    MySql Workbench syntax error

  19. 19

    Error 1452 SQL WorkBench

  20. 20

    Preventing SQL statements from getting truncated by MySQL's Workbench in `Performance Reports` section

  21. 21

    Error 1064 in MySQL Workbench when synchronising a model to a server

  22. 22

    Parameterize MySQL workbench statements: How to define variables

  23. 23

    Create function Error in phpmyadmin

  24. 24

    phpMyAdmin MySQL error # 1045

  25. 25

    Mysql Error When Changing Column INT to Double through phpmyadmin

  26. 26

    phpMyAdmin Error - SQL query

  27. 27

    Error when using export/import SQL file from phpMyAdmin

  28. 28

    MySQL trigger error 1064 when enclosing if else with multiple statements

  29. 29

    Rails and MySQL syntax error with multiple SQL statements in an execute block

HotTag

Archive