问题：
SQL无法创建表，错误号150
柴昆杰
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;
共有1个答案
干永丰
我试图执行您的DDL，我没有错误。因此，我认为问题在于，如果不存在创建表，则使用的是创建表。这意味着，如果您已经创建了表，它将不会更新结构。很可能naam字段不存在于表Fabrikant中。
您应该尝试删除架构并从头开始。
注意：您的错误代码相当于1005。假设您使用的是InnodB，您可以在下一个链接中检查您的消息。
类似资料：
-
问题内容： 尝试在mysql中创建表时出现错误。 有解决的技巧吗？ 错误： 问题答案： 查看有关外键约束的MySQL手册： 如果重新创建已删除的表，则该表必须具有符合引用该表的外键约束的定义。如前所述，它必须具有正确的列名和类型，并且必须在引用的键上具有索引。如果不满足这些条件，MySQL将返回错误号1005，并在错误消息中引用错误150。 一些想法： 最好删除表，并使用格式正确的语法对其进行新建
-
问题内容： 这是我的桌子： 现在，当我尝试导入时出现错误：＃1005-无法创建表’kobeco_yii.tbl_comments’（错误号：150）（详细信息：Percona- XtraDB，支持事务，行级锁定和外键） 问题答案： 您正在尝试在其他表之前创建。需要表和用于外键约束。 尝试将for移至其他下方。
-
问题内容： 我在Internet上搜索了此问题的解决方案，并检查了Stack Overflow问题，但是没有一种解决方案适合我的情况。 我想创建一个从表sira_no到metal_kod的外键。 该脚本返回： 我尝试将索引添加到被引用的表中： 我在两个表（字符集和排序规则）上都检查了METAL_KODU，但找不到该问题的解决方案。我该如何解决这个问题？ 这是metal_kod表： 问题答案： 错误
-
问题内容： 我当时正在数据库中创建一些表，但是每次我以errno 150结束有关外键的工作时。首先，这是我创建表的代码： 这些是我得到的错误： 我跑了，给出了更详细的错误描述： 我在StackOverflow和其他在线位置上进行了搜索- 在这里找到了一篇有用的博客文章，其中提供了有关如何解决此错误的指针 -但我无法弄清楚出了什么问题。任何帮助，将不胜感激！ 问题答案： 您应该将car_id设置为汽
-
问题内容： 我将我的MySQL数据库插入WAMP服务器时遇到了麻烦。我打算发布模式的图像，但这是我不能的第一篇文章。 下面是执行的脚本。 但是然后我得到这个错误： 我不知道为什么。谁能帮我？ 问题答案： 我迅速搜寻了你，它把我带到了这里。我引用： 如果您尝试添加名称已经在其他地方使用的约束，则会收到此消息 要检查约束，请使用以下SQL查询： 在此处查找更多信息，或尝试查看错误发生的位置。看起来像是
-
问题内容： 我正在尝试导入.sql文件及其在创建表时失败。 这是失败的查询： 我从同一数据库中导出了.sql，删除了所有表，现在我试图导入它，为什么会失败？ MySQL：无法建立表格’./dbname/data.frm’（错误号：150） 问题答案： 从MySQL-外键约束文档： 如果重新创建已删除的表，则该表必须具有符合引用该表的外键约束的定义。如前所述，它必须具有正确的列名和类型，并且必须在引