Mysql

錯誤程式碼：1215。無法添加外鍵約束 - 在 mysql 工作台中創建數據庫時

  • December 7, 2017

我在 mysql 工作台中為此數據庫創建了一個模型，但現在在執行腳本創建表時，我得到：“錯誤程式碼：1215。無法添加外鍵約束”。

當它到達腳本末尾的付款表時會彈出錯誤。過去一天我一直在研究它，根據我在網上找到的解決方案在這里和那裡進行調整，但沒有任何效果。

很感謝任何形式的幫助。

-- MySQL Script generated by MySQL Workbench
-- Thu Dec  7 14:01:08 2017
-- Model: New Model    Version: 1.0
-- 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='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema game_rental
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `game_rental` ;

-- -----------------------------------------------------
-- Schema game_rental
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `game_rental` ;
USE `game_rental` ;

-- -----------------------------------------------------
-- Table `game_rental`.`platform`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `game_rental`.`platform` ;

CREATE TABLE IF NOT EXISTS `game_rental`.`platform` (
 `platform_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(25) NOT NULL,
 PRIMARY KEY (`platform_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `game_rental`.`member`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `game_rental`.`member` ;

CREATE TABLE IF NOT EXISTS `game_rental`.`member` (
 `member_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
 `first_name` VARCHAR(45) NOT NULL,
 `last_name` VARCHAR(45) NOT NULL,
 `email` VARCHAR(50) NOT NULL,
 PRIMARY KEY (`member_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `game_rental`.`game`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `game_rental`.`game` ;

CREATE TABLE IF NOT EXISTS `game_rental`.`game` (
 `game_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
 `title` VARCHAR(255) NOT NULL,
 `publisher` VARCHAR(45) NOT NULL,
 `rental_duration` TINYINT UNSIGNED NOT NULL DEFAULT 3,
 `rental_rate` DECIMAL(4,2) NOT NULL DEFAULT 5.00,
 `replacement_cost` DECIMAL(5,2) NOT NULL DEFAULT 50.00,
 PRIMARY KEY (`game_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `game_rental`.`game_platform`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `game_rental`.`game_platform` ;

CREATE TABLE IF NOT EXISTS `game_rental`.`game_platform` (
 `game_id` SMALLINT UNSIGNED NOT NULL,
 `platform_id` TINYINT UNSIGNED NOT NULL,
 PRIMARY KEY (`game_id`, `platform_id`),
 CONSTRAINT `fk_game_id_plt`
   FOREIGN KEY (`game_id`)
   REFERENCES `game_rental`.`game` (`game_id`)
   ON DELETE RESTRICT
   ON UPDATE CASCADE,
 CONSTRAINT `fk_platform_id`
   FOREIGN KEY (`platform_id`)
   REFERENCES `game_rental`.`platform` (`platform_id`)
   ON DELETE RESTRICT
   ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE INDEX `fk_platform_id` ON `game_rental`.`game_platform` (`platform_id` ASC);


-- -----------------------------------------------------
-- Table `game_rental`.`inventory`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `game_rental`.`inventory` ;

CREATE TABLE IF NOT EXISTS `game_rental`.`inventory` (
 `inventory_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
 `game_id` SMALLINT UNSIGNED NOT NULL,
 PRIMARY KEY (`inventory_id`),
 CONSTRAINT `fk_game_id_inv`
   FOREIGN KEY (`game_id`)
   REFERENCES `game_rental`.`game` (`game_id`)
   ON DELETE RESTRICT
   ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE INDEX `idx_fk_game_id` ON `game_rental`.`inventory` (`game_id` ASC);


-- -----------------------------------------------------
-- Table `game_rental`.`rental`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `game_rental`.`rental` ;

CREATE TABLE IF NOT EXISTS `game_rental`.`rental` (
 `rental_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
 `rental_date` DATETIME NOT NULL,
 `inventory_id` MEDIUMINT UNSIGNED NOT NULL,
 `member_id` SMALLINT UNSIGNED NOT NULL,
 `return_date` DATETIME NULL DEFAULT NULL,
 `staff_id` TINYINT UNSIGNED NOT NULL,
 PRIMARY KEY (`rental_id`),
 CONSTRAINT `fk_inventory_id`
   FOREIGN KEY (`inventory_id`)
   REFERENCES `game_rental`.`inventory` (`inventory_id`)
   ON DELETE RESTRICT
   ON UPDATE CASCADE,
 CONSTRAINT `fk_member_id`
   FOREIGN KEY (`member_id`)
   REFERENCES `game_rental`.`member` (`member_id`)
   ON DELETE RESTRICT
   ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE INDEX `idx_fk_inventory_id` ON `game_rental`.`rental` (`inventory_id` ASC);

CREATE INDEX `idx_fk_member_id_rent` ON `game_rental`.`rental` (`member_id` ASC);


-- -----------------------------------------------------
-- Table `game_rental`.`payment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `game_rental`.`payment` ;

CREATE TABLE IF NOT EXISTS `game_rental`.`payment` (
 `payment_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
 `member_id` SMALLINT UNSIGNED NOT NULL,
 `rental_id` INT UNSIGNED NOT NULL,
 `amount` DECIMAL(5,2) NOT NULL,
 `payment_date` DATETIME NOT NULL,
 PRIMARY KEY (`payment_id`),
 CONSTRAINT `fk_rental_id`
   FOREIGN KEY (`rental_id`)
   REFERENCES `game_rental`.`rental` (`rental_id`)
   ON DELETE SET NULL
   ON UPDATE CASCADE,
 CONSTRAINT `fk_member_id`
   FOREIGN KEY (`member_id`)
   REFERENCES `game_rental`.`member` (`member_id`)
   ON DELETE RESTRICT
   ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE INDEX `idx_fk_member_id_pay` ON `game_rental`.`payment` (`member_id` ASC);

CREATE INDEX `fk_rental_id` ON `game_rental`.`payment` (`rental_id` ASC);


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

問題在於您的第一個外鍵付款：

CONSTRAINT `fk_rental_id`
   FOREIGN KEY (`rental_id`)
   REFERENCES `game_rental`.`rental` (`rental_id`)
   ON DELETE SET NULL
   ON UPDATE CASCADE,

基本上，您要說的是，當game_rental. rental，付款表中所有引用的rental_id都將設置為null

從文件（https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html）：

SET NULL：從父表中刪除或更新行，並將子表中的外鍵列或列設置為NULL。支持 ON DELETE SET NULL 和 ON UPDATE SET NULL 子句。

如果您指定 SET NULL 操作，請確保您沒有將子表中的列聲明為 NOT NULL。

最後一行應該澄清一切，您在付款中將rental_id 設置為NOT NULL，所以問題就來了。您應該rental_id從payment表中刪除 NOT NULL 限制，或者刪除“ ON DELETE SET NULL”。

最後; 之後你會有另一個錯誤：

Can't write; duplicate key in table 'payment'

因為名字

CONSTRAINT `fk_member_id`

用於game_rental. rental首先，您需要更改付款表中該 CONSTRAINT 的名稱，就是這樣。

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

相關問答

Mysql

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

  • October 30, 2020
檢視問答
Mysql

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

  • July 1, 2019
檢視問答
Mysql

將自動增量添加到 id 已經是外鍵的一部分

  • April 7, 2018
檢視問答
Mysql

我可以引用遠端表（MySQL）中的特定列嗎？

  • April 9, 2016
檢視問答
Mysql

MySQL 得到 errno: 150 從 Workbench 創建表

  • March 11, 2016
檢視問答
Mysql

如何從其他表中填充表的外鍵？

  • August 8, 2015
檢視問答