我慢慢变得疯狂,因为我在数据库中绑定了一个结,现在当我想在另一台服务器上创建数据库时,它会不断给出错误 .
错误发生在表'product' . 我只是给出整个脚本:
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;
错误是:
CREATE TABLE IF NOT 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
),INDEXfk_product_fabrikant1_idx
(fabrikant_naam
ASC),CONSTRAINTfk_product_fabrikant1
FOREIGN KEY (fabrikant_naam
)REFERENCESklheerde_db.fabrikant
(naam
)ON DELETE NO ACTION ON UPDATE NO ACTION)ENGINE = InnoDB AUTO_INCREMENT = 6错误代码:1005 . 无法创建表'klheerde_db.product'(错误号:150) )0.016秒
1 回答
我试图执行你的DDL我没有错误 . 所以,我认为问题在于你使用的是 CREATE TABLE IF NOT EXISTS . 这意味着如果你已经创建了表,它就不会更新结构 . 表 fabrikant 中可能不存在 naam 字段 .
您应该尝试删除架构并从头开始 .
注意:您的错误代码相当于1005.假设您正在使用InnoDB,您可以在下一个链接中检查您的消息 .
14.2.11.1. InnoDB Error Codes