Error 1005 when Forward Engineering EER diagram

2019-07-27 01:16发布

站内文章 / MySQL
54 0 0

问题:

I have created an EER diagram (as shown in the image bellow) in MySQL Workbench and wanted to Forward Engineer to build the DB.

After configuring the model options to the Target MySQL Version and removing the word "Visible" from all the Indexes in the SQL Code (the code goes in the bottom of this post), as it was triggering an error, I have came across a 1005 Error:

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

Any guidance on how to solve it is appreciated.

The SQL Code that I am using is the following:

-- 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;

Edit 1: Removed transaction_typeID from both books batch_transaction tables and ran the following SQL Script:

-- 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;

And now I got the same error in the table 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

回答1:

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

Assuming you have good reasons to store transaction_typeID in the books table, though it's functionally dependend on transactionID. Any foreign key constraint needs an index in both tables (referencing/child and referenced/parent) which can support the constraint check.

If no supporting index is found in the child table (books), the engine will create one. That's why you have the following index:

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

But the engine will not create an index in the parent table (transactions). Indexes you already have are:

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

Neither of them can support the FK constraint. So you need to define a new index in the transactions table:

INDEX (`transactionID`, `transaction_types_transaction_typeID`)

However - That is a denormalized design, which isn't something bad, if you have good reasons for it, since the consistency is enforced by the FK constraint. But if you know no good reasons for storing transaction_typeID in the books table, you should just remove it and change the FK constraint to 

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

Now it's referencing the primary key of the parent table. This is a basic design for foreign keys, and the constraint is supported by the PRIMARY KEY (index).



回答2:

Problem solved by updating the relationships in the diagram.

The first diagram was denormalized, which isn't necessarily bad if there are justifications for it (the consistency was enforced by the FK constraint).

I ended up referencing only the PK of the "parent" table, being the _types table a parent one.

Final diagram:



标签： mysql foreign-keys mysql-workbench diagram entity-relationship-model
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~