フォワードエンジニアリングEER図のエラー1005
ゴンサロペレス龚燿禄
MySQL WorkbenchでEER図（以下の画像を参照）を作成し、フォワードエンジニアにDBを構築したいと考えました。
モデルオプションをターゲットMySQLバージョンに構成し、SQLコードのすべてのインデックスから「Visible」という単語を削除した後（コードはこの投稿の下部にあります）、エラーをトリガーしていたため、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：transaction_typeID両方のbooks batch_transactionテーブルから削除し、次の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
ゴンサロペレス龚燿禄
ダイアグラムの関係を更新することで問題が解決しました。
最初の図は非正規化されましたが、正当な理由がある場合は必ずしも悪いわけではありません（一貫性はFK制約によって強制されました）。
最終的に、「親」テーブルのPKのみを参照し、その_typesテーブルが親テーブルになりました。
最終的な図：
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
編集
関連記事
Related 関連記事
- 1
MySQLWorkbenchフォワードエンジニアリングエラー
- 2
mysqlフォワードエンジニアリングエラー
- 3
MySQLでフォワードエンジニアリングを試行する際のエラー1064
- 4
SQLのリバースエンジニアリングとフォワードエンジニアリングの違いは何ですか
- 5
MySQLはフォワードエンジニアリングを支援します
- 6
ERD / MySQLでフォワードエンジニアリングを継続
- 7
「フォワードエンジニアリング」機能がmySQLワークベンチで機能しないのはなぜですか？
- 8
シーケンス図のリバースエンジニアリング
- 9
MySQLWorkbench。表の変更ステートメントとしてフォワードエンジニアリングで外部キーを生成する方法は？
- 10
管理者パスワードを見つけるためのIPカメラファームウェアのリバースエンジニアリング
- 11
MySQLWorkbenchからこのデータベースをフォワードエンジニアリングすることはできません
- 12
Hiveクエリのフォワードローリング平均
- 13
VisualParadigmでのJavaパッケージ図のリバースエンジニアリング
- 14
シーケンス図のJavaコードをリバースエンジニアリングするEclipseプラグインまたはオープンソースツール
- 15
キャレットのアウトオブフォールドvsトレーニングエラー
- 16
Eclipseの内部エラー「ポーリングのニュースフィード」
- 17
既存のデータベース「コードファースト」からのエンティティフレームワーク7リバースエンジニアリング
- 18
WP8：バックグラウンドエージェントとアプリワイヤーストレージファイル間の通信？
- 19
デルファイグリッドワースストアドプロシージャのバインド中の機能エラー
- 20
いくつかの新しい関係のフォワードエンジニアリングは、満たされたデータベースで失敗します（errno 150）
- 21
Javaでのリバースエンジニアリング
- 22
Haskell関数のリバースエンジニアリング
- 23
Python3のリバースエンジニアリング
- 24
Cubase .cpr 形式のリバース エンジニアリング
- 25
フィードフォワードニューラルネットワークのトレーニング
- 26
グーグルユニットテストフレームワークでのリンカーエラー
- 27
アセンブリからのリバースエンジニアリング最適化cコード
- 28
Kinectアプリのソースリバースエンジニアリング
- 29
デジタルフォレンジックとリバースエンジニアリングの違いは何ですか？
コメントを追加