mysql - 正向工程EER图时报错1005-6ren

mysql - 正向工程EER图时报错1005

转载作者：行者123 更新时间：2023-11-29 01:34:42

我已经在 MySQL Workbench 中创建了一个 EER 图(如下图所示)，并希望通过 Forward Engineer 来构建数据库。

在将模型选项配置为目标 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;

编辑 1:从两个 books batch_transaction 表中删除了 transaction_typeID 并运行以下 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,
  `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),
  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`)
    REFERENCES `books`.`transactions` (`transactionID`)
    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,
  `date` DATETIME NULL,
  PRIMARY KEY (`transactionID`),
  INDEX `fk_batch_transaction_transactions1_idx` (`transactions_transactionID` ASC),
  CONSTRAINT `fk_batch_transaction_transactions1`
    FOREIGN KEY (`transactions_transactionID`)
    REFERENCES `books`.`transactions` (`transactionID`)
    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;

现在我在表 book_types 中遇到了同样的错误:

ERROR: Error 1005: Can't create table `books`.`book_types` (errno: 150 "Foreign key constraint is incorrectly formed")
SQL Code:
        -- -----------------------------------------------------
        -- 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

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

最佳答案

FOREIGN KEY (`transactions_transactionID`, `transactions_transaction_types_transaction_typeID`)
    REFERENCES `books`.`transactions` (`transactionID`, `transaction_types_transaction_typeID`)

假设您有充分的理由将 transaction_typeID 存储在 books 表中，尽管它在功能上依赖于 transactionID。任何外键约束都需要在两个表(referencing/child 和 referenced/parent)中有一个索引可以支持约束检查。

如果在子表 (books) 中找不到支持索引，引擎将创建一个。这就是为什么你有以下索引:

INDEX `fk_books_transactions1_idx` (
    `transactions_transactionID` ASC,
    `transactions_transaction_types_transaction_typeID` ASC
)

但引擎不会在父表(transactions)中创建索引。您已经拥有的索引是:

PRIMARY KEY (`transactionID`),
INDEX `fk_transactions_transaction_types1_idx` (`transaction_types_transaction_typeID` ASC),

两者都不支持FK约束。所以你需要在transactions表中定义一个新的索引:

INDEX (`transactionID`, `transaction_types_transaction_typeID`)

但是 - 这是一个非规范化设计，如果您有充分的理由，这并不是什么坏事，因为一致性是由 FK 约束强制执行的。但是，如果您不知道将 transaction_typeID 存储在 books 表中的充分理由，您应该将其删除并将 FK 约束更改为

FOREIGN KEY (`transactions_transactionID`) REFERENCES `books`.`transactions` (`transactionID`)

现在它正在引用父表的主键。这是外键的基本设计，主键(索引)支持约束。

关于mysql - 正向工程EER图时报错1005，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/56821483/

文章推荐： android - 使用不匹配的驱动程序版本启用 glsl 扩展时崩溃

文章推荐： ios - SlideMenu 可在 iOS 内部屏幕上访问

MySQL - EER 表中的双主键
上下文我一直在摆弄一个小型足球爱好数据库来跟踪比赛、球员和进球。并遇到了一个我不太知道如何解决的问题。比赛表有两个外键都指向球队表中的tID。我的想法是，我稍后可以进行选择来查看在给定的比赛中哪些
mysql - EER 图编辑器不显示列数据和其他信息
在 Ubuntu 14.10 上运行 mysql-workbench 时，我在 GUI 中遇到意外行为。如果我双击 EER 图编辑器中的表，我将看不到列及其属性的列表。我的屏幕如下所示: 当我双击图表
mysql - EER 付款帮助
伙计们，我正在 Mysql Workbench 中开发我的项目并且对我的项目感到困惑。我有几个问题: 我们如何管理不同类型的付款？我的意思是我的数据库有一个支付表，我的系统有 3 种支付模式。 1]
mysql - EER 图中主键和外键的使用
在我的数据库中我有三个表(我有更多但大小写相同，用户可以是公司或个人)。 Users 有一个主键id_user； Company有一个主键id_company和一个外键users_id_user； j
mysql - 开源 - EER 建模工具
除了 MySQL Workbench 之外，还有什么好的开源或价格合理的 MySQL EER 建模工具？我发现 MySQL Workbench 界面笨拙。我希望能够管理我的生产模式，开始在 EER 中
MySQL - EER 图 - 组必须具有某种类型的成员
我正在尝试创建一个数据库，我的数据库的一部分是，有不同的组，其中有成员。有一些要求，一个团体必须有一个穿着红色衣服的成员，一个穿着黄色衣服的成员和一个穿着蓝色衣服的成员。它可以让成员穿着其他颜色，但该
mysql - 更改工作台 EER 图中表的数据库名称
我正在制作 EER 图并陷入困境:我错误地忘记了更改为正确的数据库，并在错误的数据库中创建了所有表。有人可以帮我解决这个问题吗？最佳答案您可以做的就是复制概述页面中的表并将其粘贴到其他架构中。然
mysql - 在 EER 中应用外键限制
只允许来自同一国家/地区的人一起聊天。如何使用 ERM 实现这一目标？最佳答案您不会在数据库中执行此操作，您将使用您的编程语言检查玩家是否位于同一个国家/地区。关于mysql - 在 EER 中
MySQL Workbench EER 图创建
这里有两个关于 MySQL Workbench 的问题。有没有办法用新创建的数据库表更新现有的 EER 图，以便 EER 图自动反射(reflect)新表及其关系。或有没有办法在从现有数据库创建
MySQL Workbench EER - 显示表中的所有列
如何在 EER 模型中显示表格中的所有列？我已经从现有数据库生成了 EER 模型，但是一个主表很大(包含很多列)并且 EER 只显示前 30 列(还有一个注释“还有 28 个......”) 如何扩展
mysql - MySQL 工作台中的 EER
我在 MySQL Workbench 中有一个问题。如何使 1 到 0..1 的关系直观地表示出来？我只找到了这个符号: 最佳答案这并不明显，但要将 1:1 变为 1:0-1，您必须更改关系编辑
mysql - 数据库模型所需的建议 - 附有部分 EER
我开始学习cakephp 3，这将是我的学习项目。应用的思想是每个用户可以拥有很多产品，用户之间可以进行产品交易。工作流程: 所以一个用户可以访问另一个用户的个人资料，他可以点击按钮进行交易。这将显示
database - EER 图，不同的符号？
有时，当我使用 EER 图时，我会看到不同的符号，例如，'d'、'o'、'u' 等。 Like the 'd' here 它们是什么意思，它们的用途是什么？它存在多少种变体？最佳答案 1.d 表示“
MySQL Workbench EER 图更改表格颜色
从现有数据库创建 EER 图后，由于表头和表名文本均为黑色，我无法读取图中的表名。我不确定为什么会这样，因为我上次使用它时不是这样。有谁知道是否有办法改变图表中使用的颜色？我已经搜索了所有菜单，但没有
entity-relationship - 符号放置的 EER 子类型差异
我有一个关于在 EER 模型中使用子类型符号的简单问题。我在下面给出了示例图。我的问题是使用子类型符号 "U" 有什么区别？在从父级到子类型或从子类型到父级的行中，为了更清楚，请参见下面的示例图。最
java - Jackson:序列化 EER 特化
我使用 spring boot 和 jackson 来提供 API。在我的数据库中有一个泛化/特化字段。这意味着，我有两个字段:一个值和一个定义该值的数据类型的字段。所以我的实体看起来像这样: pu
MySQL Workbench 不保存 EER 图
我已经使用 WB 很长时间了，图表功能绝对是我最喜欢的功能之一，在我的一个项目中，当我拿到 4x4 A4 页面时，超过 50 个表格在图表中得到了很好的布局打印出来的图我真的觉得不错! 然而今天，当我
MySQL Workbench - 在单个图表中工作时 EER 模型中的所有图表都会更新
我为一个项目创建了一个 EER 模型，模型中有几个图表。我的问题是我在所有图表中都有同名的表，当我更新一个表时，所有同名的表都会遭受相同的更改。我怎样才能避免这种行为并使图表独立于其他图表？最佳
MySQL Workbench 在 EER 建模中加入架构
我不知道这是怎么发生的，但我正在创建一个 EER 图，当我插入表并创建它们之间各自的关系时，我注意到 Workbench 将它们分为两种不同的模式。 Mydb 和 Mydb2 分别有 10 个和 26
mysql - 从现有数据库创建 EER 模型但已完成关系 - Workbench
我正在尝试将现有数据库导入到 mysqlq Workbench。我可以做这个。它运行完美。然而，该过程只是导入表，而没有它们之间的关系。如何导入数据库并创建具有它们之间关系的 eer 模型？最佳答

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

mysql - 正向工程EER图时报错1005