Mysql

正向工程 EER 圖時出現錯誤 1005

  • July 1, 2019

我在 MySQL Workbench 中創建了一個 EER 圖（如下圖所示），並希望轉發工程師來建構數據庫。

EER圖

在將模型選項配置為目標 MySQL 版本並從 SQL 程式碼中的所有索引中刪除“可見”一詞後（程式碼在這篇文章的底部），因為它觸發了一個錯誤，我遇到了 1005錯誤：

Executing SQL script in server
ERROR: Error 1005: Can't create table `books`.`books` (errno: 150 "Foreign key constraint is incorrectly formed")
SQL Code:
       -- -----------------------------------------------------
       -- Table `books`.`books`
       -- -----------------------------------------------------
       CREATE TABLE IF NOT EXISTS `books`.`books` (
         `bookID` INT(11) NOT NULL AUTO_INCREMENT,
         `name` VARCHAR(255) NULL,
         `price` DECIMAL(10,2) NULL,
         `book_prices_book_priceID` INT(11) NOT NULL,
         `book_types_book_typeID` INT(11) NOT NULL,
         `transactions_transactionID` INT(11) NOT NULL,
         `transactions_transaction_types_transaction_typeID` INT(11) NOT NULL,
         `ISBN` VARCHAR(13) NULL,
         PRIMARY KEY (`bookID`),
         INDEX `fk_books_book_prices1_idx` (`book_prices_book_priceID` ASC),
         INDEX `fk_books_transactions1_idx` (`transactions_transactionID` ASC, `transactions_transaction_types_transaction_typeID` ASC),
         CONSTRAINT `fk_books_book_prices1`
           FOREIGN KEY (`book_prices_book_priceID`)
           REFERENCES `books`.`book_prices` (`book_priceID`)
           ON DELETE NO ACTION
           ON UPDATE NO ACTION,
         CONSTRAINT `fk_books_transactions1`
           FOREIGN KEY (`transactions_transactionID` , `transactions_transaction_types_transaction_typeID`)
           REFERENCES `books`.`transactions` (`transactionID` , `transaction_types_transaction_typeID`)
           ON DELETE NO ACTION
           ON UPDATE NO ACTION)
       ENGINE = InnoDB

SQL script execution finished: statements: 8 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

任何有關如何解決它的指導表示讚賞。

我使用的 SQL 程式碼如下：

-- MySQL Workbench Forward Engineering

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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema books
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema books
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `books` DEFAULT CHARACTER SET utf8 ;
USE `books` ;

-- -----------------------------------------------------
-- Table `books`.`book_prices`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `books`.`book_prices` (
 `book_priceID` INT(11) NOT NULL AUTO_INCREMENT,
 `bookID` INT(11) NULL,
 `price` DECIMAL(10,2) NULL,
 `currency` CHAR(2) NULL,
 `date_start` DATETIME NULL,
 `date_end` DATETIME NULL,
 PRIMARY KEY (`book_priceID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `books`.`transaction_types`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `books`.`transaction_types` (
 `transaction_typeID` INT(11) NOT NULL AUTO_INCREMENT,
 `transactionID` INT(11) NULL,
 PRIMARY KEY (`transaction_typeID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `books`.`transactions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `books`.`transactions` (
 `transactionID` INT(11) NOT NULL AUTO_INCREMENT,
 `transaction_types_transaction_typeID` INT(11) NOT NULL,
 `date` DATETIME NULL,
 PRIMARY KEY (`transactionID`),
 INDEX `fk_transactions_transaction_types1_idx` (`transaction_types_transaction_typeID` ASC),
 CONSTRAINT `fk_transactions_transaction_types1`
   FOREIGN KEY (`transaction_types_transaction_typeID`)
   REFERENCES `books`.`transaction_types` (`transaction_typeID`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `books`.`books`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `books`.`books` (
 `bookID` INT(11) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(255) NULL,
 `price` DECIMAL(10,2) NULL,
 `book_prices_book_priceID` INT(11) NOT NULL,
 `book_types_book_typeID` INT(11) NOT NULL,
 `transactions_transactionID` INT(11) NOT NULL,
 `transactions_transaction_types_transaction_typeID` INT(11) NOT NULL,
 `ISBN` VARCHAR(13) NULL,
 PRIMARY KEY (`bookID`),
 INDEX `fk_books_book_prices1_idx` (`book_prices_book_priceID` ASC),
 INDEX `fk_books_transactions1_idx` (`transactions_transactionID` ASC, `transactions_transaction_types_transaction_typeID` ASC),
 CONSTRAINT `fk_books_book_prices1`
   FOREIGN KEY (`book_prices_book_priceID`)
   REFERENCES `books`.`book_prices` (`book_priceID`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
 CONSTRAINT `fk_books_transactions1`
   FOREIGN KEY (`transactions_transactionID` , `transactions_transaction_types_transaction_typeID`)
   REFERENCES `books`.`transactions` (`transactionID` , `transaction_types_transaction_typeID`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `books`.`batch_transaction`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `books`.`batch_transaction` (
 `transactionID` INT(11) NOT NULL AUTO_INCREMENT,
 `batchID` INT(11) NOT NULL,
 `transactions_transactionID` INT(11) NOT NULL,
 `transactions_transaction_types_transaction_typeID` INT(11) NOT NULL,
 `date` DATETIME NULL,
 PRIMARY KEY (`transactionID`),
 INDEX `fk_batch_transaction_transactions1_idx` (`transactions_transactionID` ASC, `transactions_transaction_types_transaction_typeID` ASC),
 CONSTRAINT `fk_batch_transaction_transactions1`
   FOREIGN KEY (`transactions_transactionID` , `transactions_transaction_types_transaction_typeID`)
   REFERENCES `books`.`transactions` (`transactionID` , `transaction_types_transaction_typeID`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `books`.`batches`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `books`.`batches` (
 `batchID` INT(11) NOT NULL AUTO_INCREMENT,
 `batch_transaction_transactionID` INT(11) NULL,
 `book_typeID` INT(11) NOT NULL,
 `price` DECIMAL(10,2) NULL,
 `supplierID` INT(11) NULL,
 PRIMARY KEY (`batchID`),
 INDEX `fk_batches_batch_transaction1_idx` (`batch_transaction_transactionID` ASC),
 CONSTRAINT `fk_batches_batch_transaction1`
   FOREIGN KEY (`batch_transaction_transactionID`)
   REFERENCES `books`.`batch_transaction` (`transactionID`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `books`.`book_types`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `books`.`book_types` (
 `book_typeID` INT(11) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(255) NULL,
 `books_bookID` INT(11) NOT NULL,
 `books_book_prices_book_priceID` INT(11) NOT NULL,
 `books_book_types_book_typeID` INT(11) NOT NULL,
 `batches_batchID` INT(11) NOT NULL,
 PRIMARY KEY (`book_typeID`),
 INDEX `fk_product_types_products1_idx` (`books_bookID` ASC, `books_book_prices_book_priceID` ASC, `books_book_types_book_typeID` ASC),
 INDEX `fk_product_types_batches1_idx` (`batches_batchID` ASC),
 CONSTRAINT `fk_book_types_books1`
   FOREIGN KEY (`books_bookID` , `books_book_prices_book_priceID` , `books_book_types_book_typeID`)
   REFERENCES `books`.`books` (`bookID` , `book_prices_book_priceID` , `book_types_book_typeID`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
 CONSTRAINT `fk_book_types_batches1`
   FOREIGN KEY (`batches_batchID`)
   REFERENCES `books`.`batches` (`batchID`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `books`.`suppliers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `books`.`suppliers` (
 `supplierID` INT(11) NOT NULL AUTO_INCREMENT,
 `batches_batchID` INT(11) NOT NULL,
 `name` VARCHAR(255) NULL,
 PRIMARY KEY (`supplierID`),
 INDEX `fk_suppliers_batches1_idx` (`batches_batchID` ASC),
 CONSTRAINT `fk_suppliers_batches1`
   FOREIGN KEY (`batches_batchID`)
   REFERENCES `books`.`batches` (`batchID`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `books`.`customer_transaction`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `books`.`customer_transaction` (
 `transactionID` INT(11) NOT NULL AUTO_INCREMENT,
 `customerID` INT(11) NULL,
 `transactions_transactionID` INT(11) NOT NULL,
 `date` DATETIME NULL,
 PRIMARY KEY (`transactionID`),
 INDEX `fk_client_transaction_transactions1_idx` (`transactions_transactionID` ASC),
 CONSTRAINT `fk_customer_transaction_transactions1`
   FOREIGN KEY (`transactions_transactionID`)
   REFERENCES `books`.`transactions` (`transactionID`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `books`.`customers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `books`.`customers` (
 `customerID` INT(11) NOT NULL AUTO_INCREMENT,
 `books_bookID` INT(11) NULL,
 `books_book_prices_book_priceID` INT(11) NOT NULL,
 `books_book_types_book_typeID` INT(11) NOT NULL,
 `customer_transaction_transactionID` INT(11) NOT NULL,
 PRIMARY KEY (`customerID`),
 INDEX `fk_clients_products1_idx` (`books_bookID` ASC, `books_book_prices_book_priceID` ASC, `books_book_types_book_typeID` ASC),
 INDEX `fk_clients_client_transaction1_idx` (`customer_transaction_transactionID` ASC),
 CONSTRAINT `fk_customrs_products1`
   FOREIGN KEY (`books_bookID` , `books_book_prices_book_priceID` , `books_book_types_book_typeID`)
   REFERENCES `books`.`books` (`bookID` , `book_prices_book_priceID` , `book_types_book_typeID`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
 CONSTRAINT `fk_customers_customer_transaction1`
   FOREIGN KEY (`customer_transaction_transactionID`)
   REFERENCES `books`.`customer_transaction` (`transactionID`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `books`.`discounts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `books`.`discounts` (
 `discountID` INT(11) NOT NULL AUTO_INCREMENT,
 `Name` VARCHAR(255) NOT NULL,
 `transactions_transactionID` INT(11) NULL,
 `transactions_transaction_types_transaction_typeID` INT(11) NULL,
 PRIMARY KEY (`discountID`),
 INDEX `fk_discounts_transactions1_idx` (`transactions_transactionID` ASC, `transactions_transaction_types_transaction_typeID` ASC),
 CONSTRAINT `fk_discounts_transactions1`
   FOREIGN KEY (`transactions_transactionID` , `transactions_transaction_types_transaction_typeID`)
   REFERENCES `books`.`transactions` (`transactionID` , `transaction_types_transaction_typeID`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `books`.`discount_types`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `books`.`discount_types` (
 `discount_typeID` INT(11) NOT NULL AUTO_INCREMENT,
 `Type` VARCHAR(255) NULL,
 `discounts_discountID` INT(11) NOT NULL,
 PRIMARY KEY (`discount_typeID`),
 INDEX `fk_discount_types_discounts1_idx` (`discounts_discountID` ASC),
 CONSTRAINT `fk_discount_types_discounts1`
   FOREIGN KEY (`discounts_discountID`)
   REFERENCES `books`.`discounts` (`discountID`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `books`.`storagedistribution`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `books`.`storagedistribution` (
 `storagedistributionID` INT(11) NOT NULL AUTO_INCREMENT,
 `Name` VARCHAR(255) NULL,
 `transactions_transactionID` INT(11) NOT NULL,
 `transactions_transaction_types_transaction_typeID` INT(11) NOT NULL,
 PRIMARY KEY (`storagedistributionID`),
 INDEX `fk_storagedistribution_transactions1_idx` (`transactions_transactionID` ASC, `transactions_transaction_types_transaction_typeID` ASC),
 CONSTRAINT `fk_storagedistribution_transactions1`
   FOREIGN KEY (`transactions_transactionID` , `transactions_transaction_types_transaction_typeID`)
   REFERENCES `books`.`transactions` (`transactionID` , `transaction_types_transaction_typeID`)
   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;

通過更新圖中的關係解決了問題。

第一個圖是非規範化的，如果有理由，這不一定是壞事（一致性由 FK 約束強制執行）。

我最終只引用了“父”表的 PK，作為父表_types。

最終圖：

最終 EER 圖

錯誤 150：

方案A：重新排列順序CREATE TABLEs

計劃 B：禁用 FK，創建表，啟用 FK。

計劃 C：創建沒有 FK 的表，然後添加ALTERs以添加 FK。

引用自：https://dba.stackexchange.com/questions/241693

相關問答

Mysql

錯誤程式碼：1215。無法添加外鍵約束

  • May 29, 2021
檢視問答
Mysql

使用逆向工程（Mac OS）時，MySQL Workbench 不顯示表之間的關係

  • October 30, 2020
檢視問答
Mysql

errno: 150 ‘外鍵約束格式不正確’

  • September 29, 2019
檢視問答
Mysql

是否可以更改 MySQL Workbench 圖表中關係線的顏色？

  • May 23, 2019
檢視問答
Mysql

你知道這是非辨識或辨識關係的例子嗎？

  • August 29, 2018
檢視問答
Mysql

mySQL - 無法添加外鍵約束

  • August 23, 2018
檢視問答