SQL can't create table, errno 150

0
votes

I'm slowly going crazy because I tied a knot with keys in a database and now when I want to create the database on an other server, it constantly gives back an error.

The error occurs at the table 'product'. I'll just give the entire script:

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 `sql324208` DEFAULT CHARACTER SET utf8 ;
USE `sql324208` ;

-- -----------------------------------------------------
-- Table `sql324208`.`adres`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sql324208`.`adres` (
  `straatnr` INT(11) NOT NULL,
  `postcode` VARCHAR(45) NOT NULL,
  `plaats` VARCHAR(45) NOT NULL,
  `adresid` INT(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`adresid`))
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `sql324208`.`bak`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sql324208`.`bak` (
  `baknr` INT(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`baknr`))
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `sql324208`.`fabrikant`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sql324208`.`fabrikant` (
  `naam` VARCHAR(45) NOT NULL,
  `contactpersoon` VARCHAR(45) NULL DEFAULT NULL,
  `telefoonnr` INT(11) NOT NULL,
  `internetadres` VARCHAR(45) NULL DEFAULT NULL,
  `adresid` INT(11) NOT NULL,
  PRIMARY KEY (`naam`),
  INDEX `fk_fabrikant_adres1_idx` (`adresid` ASC),
  CONSTRAINT `fk_fabrikant_adres1`
    FOREIGN KEY (`adresid`)
    REFERENCES `sql324208`.`adres` (`adresid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `sql324208`.`klantkorting`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sql324208`.`klantkorting` (
  `kortingsid` INT(11) NOT NULL AUTO_INCREMENT,
  `jaaromzet` DECIMAL(12,2) NULL DEFAULT NULL,
  `jaar` YEAR NULL DEFAULT NULL,
  `kortingspercentage` INT(11) NOT NULL,
  PRIMARY KEY (`kortingsid`))
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `sql324208`.`klant`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sql324208`.`klant` (
  `naam` VARCHAR(45) NOT NULL,
  `klantid` INT(11) NOT NULL AUTO_INCREMENT,
  `adresid` INT(11) NOT NULL,
  `kortingid` INT NOT NULL,
  PRIMARY KEY (`klantid`),
  INDEX `fk_klant_adres1_idx` (`adresid` ASC),
  INDEX `k_kk_idx` (`kortingid` ASC),
  CONSTRAINT `fk_klant_adres1`
    FOREIGN KEY (`adresid`)
    REFERENCES `sql324208`.`adres` (`adresid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `k_kk`
    FOREIGN KEY (`kortingid`)
    REFERENCES `sql324208`.`klantkorting` (`kortingsid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 6;


-- -----------------------------------------------------
-- Table `sql324208`.`medewerker`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sql324208`.`medewerker` (
  `medewerkerID` INT(11) NOT NULL AUTO_INCREMENT,
  `naam` VARCHAR(45) NOT NULL,
  `afdeling` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`medewerkerID`))
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `sql324208`.`verkoop-order`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sql324208`.`verkoop-order` (
  `verkoop-orderid` INT(11) NOT NULL AUTO_INCREMENT,
  `status` VARCHAR(45) NOT NULL,
  `klantid` INT(11) NOT NULL,
  `medewerkerID` INT(11) NOT NULL,
  PRIMARY KEY (`verkoop-orderid`),
  INDEX `fk_verkoop-order_klant1_idx` (`klantid` ASC),
  INDEX `fk_verkoop-order_medewerker1_idx` (`medewerkerID` ASC),
  CONSTRAINT `fk_verkoop-order_klant1`
    FOREIGN KEY (`klantid`)
    REFERENCES `sql324208`.`klant` (`klantid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_verkoop-order_medewerker1`
    FOREIGN KEY (`medewerkerID`)
    REFERENCES `sql324208`.`medewerker` (`medewerkerID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `sql324208`.`factuur`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sql324208`.`factuur` (
  `verkoop-orderid` INT(11) NOT NULL AUTO_INCREMENT,
  `factuur-status` VARCHAR(45) NOT NULL,
  `verzend-datum` DATE NOT NULL,
  `betaal-datum` DATE NULL DEFAULT NULL,
  `verzend-adresid` INT NOT NULL,
  `bestel-adresid` INT NOT NULL,
  PRIMARY KEY (`verkoop-orderid`, `verzend-datum`),
  INDEX `f_a_idx` (`verzend-adresid` ASC),
  INDEX `f_a2_idx` (`bestel-adresid` ASC),
  CONSTRAINT `fk_factuur_verkoop-order1`
    FOREIGN KEY (`verkoop-orderid`)
    REFERENCES `sql324208`.`verkoop-order` (`verkoop-orderid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `f_a`
    FOREIGN KEY (`verzend-adresid`)
    REFERENCES `sql324208`.`adres` (`adresid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `f_a2`
    FOREIGN KEY (`bestel-adresid`)
    REFERENCES `sql324208`.`adres` (`adresid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 6;


-- -----------------------------------------------------
-- Table `sql324208`.`gang`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sql324208`.`gang` (
  `gangid` VARCHAR(1) NOT NULL,
  PRIMARY KEY (`gangid`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `sql324208`.`product`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sql324208`.`product` (
  `productnr` INT(11) NOT NULL AUTO_INCREMENT,
  `naam` VARCHAR(45) NOT NULL,
  `bestelcode` VARCHAR(45) NULL DEFAULT NULL,
  `verpakking` VARCHAR(45) NULL DEFAULT NULL,
  `fabrikant_naam` VARCHAR(45) NOT NULL,
  `hoeveelheid_in_voorraad` INT(11) NOT NULL,
  PRIMARY KEY (`productnr`),
  INDEX `fk_product_fabrikant1_idx` (`fabrikant_naam` ASC),
  CONSTRAINT `fk_product_fabrikant1`
    FOREIGN KEY (`fabrikant_naam`)
    REFERENCES `sql324208`.`fabrikant` (`naam`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 6;


-- -----------------------------------------------------
-- Table `sql324208`.`inkoop-order_producten`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sql324208`.`inkoop-order_producten` (
  `productnr` INT(11) NOT NULL,
  `inkoop-ordernr` INT(11) NOT NULL,
  `aantal` INT(11) NOT NULL,
  PRIMARY KEY (`productnr`, `inkoop-ordernr`),
  INDEX `fk_product_has_inkoop-order_inkoop-order1_idx` (`inkoop-ordernr` ASC),
  INDEX `fk_product_has_inkoop-order_product1_idx` (`productnr` ASC),
  CONSTRAINT `iop_p`
    FOREIGN KEY (`productnr`)
    REFERENCES `sql324208`.`product` (`productnr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sql324208`.`inkoop-order`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sql324208`.`inkoop-order` (
  `inkoop-ordernr` INT(11) NOT NULL AUTO_INCREMENT,
  `leverdatum` DATETIME NULL DEFAULT NULL,
  `besteldatum` DATETIME NOT NULL,
  `medewerkerID` INT(11) NOT NULL,
  PRIMARY KEY (`inkoop-ordernr`),
  INDEX `fk_inkoop-order_medewerker1_idx` (`medewerkerID` ASC),
  CONSTRAINT `io_iop`
    FOREIGN KEY (`inkoop-ordernr`)
    REFERENCES `sql324208`.`inkoop-order_producten` (`inkoop-ordernr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_inkoop-order_medewerker1`
    FOREIGN KEY (`medewerkerID`)
    REFERENCES `sql324208`.`medewerker` (`medewerkerID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 6;


-- -----------------------------------------------------
-- Table `sql324208`.`prijs`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sql324208`.`prijs` (
  `productnr` INT(11) NOT NULL,
  `datum` DATE NOT NULL,
  `prijs` DECIMAL(12,2) NOT NULL,
  PRIMARY KEY (`productnr`, `datum`),
  CONSTRAINT `fk_prijs_product1`
    FOREIGN KEY (`productnr`)
    REFERENCES `sql324208`.`product` (`productnr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `sql324208`.`product-locatie`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sql324208`.`product-locatie` (
  `schapnr` INT(11) NOT NULL AUTO_INCREMENT,
  `productnr` INT(11) NOT NULL,
  `gangid` VARCHAR(1) NOT NULL,
  PRIMARY KEY (`schapnr`),
  INDEX `fk_product-locatie_product1_idx` (`productnr` ASC),
  INDEX `fk_product-locatie_gang1_idx` (`gangid` ASC),
  CONSTRAINT `fk_product-locatie_gang1`
    FOREIGN KEY (`gangid`)
    REFERENCES `sql324208`.`gang` (`gangid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_product-locatie_product1`
    FOREIGN KEY (`productnr`)
    REFERENCES `sql324208`.`product` (`productnr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 6;


-- -----------------------------------------------------
-- Table `sql324208`.`robot`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sql324208`.`robot` (
  `robotnr` INT(11) NOT NULL AUTO_INCREMENT,
  `gangid` VARCHAR(1) NOT NULL,
  `status` VARCHAR(45) NOT NULL COMMENT 'Status voorbeeld:\nverwerken order <verkoop-orderid>, XX%',
  PRIMARY KEY (`robotnr`),
  INDEX `gang_idx` (`gangid` ASC),
  CONSTRAINT `gang`
    FOREIGN KEY (`gangid`)
    REFERENCES `sql324208`.`gang` (`gangid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 6;


-- -----------------------------------------------------
-- Table `sql324208`.`verkoop-order_robots`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sql324208`.`verkoop-order_robots` (
  `verkoop-orderid` INT(11) NOT NULL,
  `baknr` INT(11) NOT NULL,
  `robotnr` INT NOT NULL,
  PRIMARY KEY (`verkoop-orderid`, `robotnr`),
  INDEX `fk_robot_has_verkoop-order_verkoop-order1_idx` (`verkoop-orderid` ASC),
  INDEX `fk_robot_has_verkoop-order_bak1_idx` (`baknr` ASC),
  INDEX `ro_r_idx` (`robotnr` ASC),
  CONSTRAINT `fk_robot_has_verkoop-order_bak1`
    FOREIGN KEY (`baknr`)
    REFERENCES `sql324208`.`bak` (`baknr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_robot_has_verkoop-order_verkoop-order1`
    FOREIGN KEY (`verkoop-orderid`)
    REFERENCES `sql324208`.`verkoop-order` (`verkoop-orderid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `ro_r`
    FOREIGN KEY (`robotnr`)
    REFERENCES `sql324208`.`robot` (`robotnr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 6;


-- -----------------------------------------------------
-- Table `sql324208`.`verkoop-order_producten`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sql324208`.`verkoop-order_producten` (
  `productnr` INT(11) NOT NULL,
  `verkoop-orderid` INT(11) NOT NULL,
  `aantal` INT(11) NOT NULL,
  PRIMARY KEY (`productnr`, `verkoop-orderid`),
  INDEX `fk_product_has_verkoop-order_verkoop-order1_idx` (`verkoop-orderid` ASC),
  INDEX `fk_product_has_verkoop-order_product1_idx` (`productnr` ASC),
  CONSTRAINT `vo_pr`
    FOREIGN KEY (`productnr`)
    REFERENCES `sql324208`.`product` (`productnr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `vo_vop`
    FOREIGN KEY (`verkoop-orderid`)
    REFERENCES `sql324208`.`verkoop-order` (`verkoop-orderid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sql324208`.`inkoop-prijs`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sql324208`.`inkoop-prijs` (
  `productnr` INT(11) NOT NULL,
  `datum` DATE NOT NULL,
  `prijs` DECIMAL(12,2) NOT NULL,
  PRIMARY KEY (`productnr`, `datum`),
  INDEX `fk_inkoop-prijs_product1_idx` (`productnr` ASC),
  CONSTRAINT `fk_inkoop-prijs_product1`
    FOREIGN KEY (`productnr`)
    REFERENCES `sql324208`.`product` (`productnr`)
    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;

The error is:

CREATE TABLE IF NOT EXISTS `klheerde_db.product` ( `productnr` INT(11) NOT NULL AUTO_INCREMENT, `naam` VARCHAR(45) NOT NULL, `bestelcode` VARCHAR(45) NULL DEFAULT NULL, `verpakking` VARCHAR(45) NULL DEFAULT NULL, `fabrikant_naam` VARCHAR(45) NOT NULL, `hoeveelheid_in_voorraad` INT(11) NOT NULL, PRIMARY KEY (`productnr`), INDEX `fk_product_fabrikant1_idx` (`fabrikant_naam` ASC), CONSTRAINT `fk_product_fabrikant1` FOREIGN KEY (`fabrikant_naam`) REFERENCES `klheerde_db.fabrikant` (`naam`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB AUTO_INCREMENT = 6
Error Code: 1005. Can't create table 'klheerde_db.product' (errno: 150) 0.016 sec

2
mysqlsqldatabaseerrno
Here's a bit of info on errno 105 that lists possible causes: link – Slicedpan
@Ken What is the schema name - klheerde_db? There is such database in your script. – Devart
I read it, but all is equal. I checked that, I also tried it without AI, still no luck. I really am stuck... – Ken
well that's because I replaced the database name to put it on the new server – Ken
I have tryed the script. All tables are created. – Devart

2 Answers

1
votes

I tried to execute your DDL I had no error. So, I think that the problem is that you are using a CREATE TABLE IF NOT EXISTS. That implies if you have already the table created it wont update the structure. It is likely that the field naam doesn't exists in the table fabrikant.

You should try to drop your schema and start again from the beginning.

Note: Your error code is equivalent to 1005. You can check your messages in the next link assuming you're using InnoDB.

14.2.11.1. InnoDB Error Codes

0
votes

I this case the problem is different tables encoding. For solve this you need to add DEFAULT CHARACTER SET = utf8 part to all tables:

CREATE TABLE IF NOT EXISTS `product` (
  `productnr` INT(11) NOT NULL AUTO_INCREMENT,
  `naam` VARCHAR(45) NOT NULL,
  `bestelcode` VARCHAR(45) NULL DEFAULT NULL,
  `verpakking` VARCHAR(45) NULL DEFAULT NULL,
  `fabrikant_naam` VARCHAR(45) NOT NULL,
  `hoeveelheid_in_voorraad` INT(11) NOT NULL,
  PRIMARY KEY (`productnr`),
  INDEX `fk_product_fabrikant1_idx` (`fabrikant_naam`),
  CONSTRAINT `fk_product_fabrikant1`
    FOREIGN KEY (`fabrikant_naam`)
    REFERENCES `fabrikant` (`naam`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
AUTO_INCREMENT = 6;

Look SQLize.online for working solution