主页 / dba / 问题 / 68157
Accepted
Jakubee
Asked: 2014-06-13 07:01:48 +0800 CST

创建表时无法添加外键约束

  • 772

我的错误代码是 1215，它发现外键约束有问题。我知道所有字段都是相同的数据类型。我看不出问题出在哪里。任何帮助将非常感激！谢谢！

这是ERD：在此处输入图像描述

和架构：

=== 交易表 ===

CREATE TABLE IF NOT EXISTS `test`.`Transaction` (
  `transaction_id` SMALLINT(5) NOT NULL,
  `customer_id` SMALLINT NULL,
  `copy_id` DECIMAL(5) NOT NULL,
  `transaction_date` DATE NOT NULL,
  `transaction_amount` DECIMAL(3,2) NULL,
  `transaction_comment` VARCHAR(60) NULL,
  `previous_transaction_id` SMALLINT(5) NOT NULL,
  `transaction_type_code` VARCHAR(10) NOT NULL,
  `employee_emp_id` TINYINT NOT NULL,
  INDEX `fk_Copy_Id_idx` (`copy_id` ASC),
  INDEX `fk_Transaction_Transaction_Type1_idx` (`transaction_type_code` ASC),
  PRIMARY KEY (`transaction_id`),
  INDEX `fk_Previous_Transaction_Id_idx` (`previous_transaction_id` ASC),
  INDEX `fk_Transaction_Emp_idx` (`employee_emp_id` ASC),
  INDEX `fk_Transaction_Customer_Id_idx` (`customer_id` ASC),
  CONSTRAINT `fk_Copy_Id`
    FOREIGN KEY (`copy_id`)
    REFERENCES `test`.`Customer_Rentals` (`copy_id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Previous_Transaction_Id`
    FOREIGN KEY (`previous_transaction_id`)
    REFERENCES `test`.`Transaction` (`transaction_id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Transaction_Type_Code`
    FOREIGN KEY (`transaction_type_code`)
    REFERENCES `test`.`Transaction_Type` (`transaction_type_code`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Transaction_Emp`
    FOREIGN KEY (`employee_emp_id`)
    REFERENCES `test`.`Employee` (`emp_Id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Transaction_Customer_Id`
    FOREIGN KEY (`customer_id`)
    REFERENCES `test`.`Customer` (`custumer_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

=== 客户租金 ===

CREATE TABLE IF NOT EXISTS `test`.`Customer_Rentals` (
  `copy_id` DECIMAL(5) NOT NULL,
  `customer_id` SMALLINT NOT NULL,
  `movie_id` SMALLINT NULL,
  `status_code` VARCHAR(10) NOT NULL,
  `date_out` DATETIME NOT NULL,
  `date_returned` DATETIME NOT NULL,
  `amount_due` DECIMAL(4,2) NOT NULL DEFAULT 00.00,
  PRIMARY KEY (`copy_id`),
  INDEX `fk_Customer_Id_idx` (`customer_id` ASC),
  INDEX `fk_Movie_Id_idx` (`movie_id` ASC),
  INDEX `fk_Rent_Status_Code_idx` (`status_code` ASC),
  CONSTRAINT `fk_Customer_Id`
    FOREIGN KEY (`customer_id`)
    REFERENCES `test`.`Customer` (`custumer_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Movie_Id`
    FOREIGN KEY (`movie_id`)
    REFERENCES `test`.`Movie` (`movie_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Rent_Status_Code`
    FOREIGN KEY (`status_code`)
    REFERENCES `test`.`Rent_Status` (`rental_status_code`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

=== 交易类型表 ===

CREATE TABLE IF NOT EXISTS `test`.`Transaction_Type` (
  `transaction_type_code` VARCHAR(10) NOT NULL,
  `transaction_type_desc` ENUM('Payment','Refund') NULL,
  PRIMARY KEY (`transaction_type_code`),
  INDEX `transaction_type_code` (`transaction_type_code` ASC))
ENGINE = InnoDB;

=== 员工 ===

CREATE TABLE IF NOT EXISTS `test`.`Employee` (
  `emp_Id` TINYINT NOT NULL AUTO_INCREMENT,
  `emp_DoB` DATE NULL,
  `first_name` VARCHAR(45) NOT NULL,
  `last_name` VARCHAR(45) NULL,
  `hire_date` DATE NOT NULL,
  PRIMARY KEY (`emp_Id`))
ENGINE = InnoDB;

=== 客户表 ===

CREATE TABLE IF NOT EXISTS `test`.`Customer` (
  `custumer_id` SMALLINT NOT NULL AUTO_INCREMENT,
  `membership` VARCHAR(15) NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `surname` VARCHAR(20) NOT NULL,
  `address` VARCHAR(45) NOT NULL,
  `custumer_email` VARCHAR(45) NOT NULL,
  `dataOfBirth` DATE NULL,
  `sex` TINYTEXT NULL,
  `dateOfRegistration` DATETIME NOT NULL,
  `active` TINYINT(1) NOT NULL,
  PRIMARY KEY (`custumer_id`),
  INDEX `lastname` (`surname` ASC))
ENGINE = InnoDB;

======================= 编辑=========================== =====

似乎外键选项不能未定义！

CONSTRAINT `fk_Rent_Status_Code`
    FOREIGN KEY (`status_code`)
    REFERENCES `test`.`Rent_Status` (`rental_status_code`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

必须改为：

CONSTRAINT `fk_Rent_Status_Code`
    FOREIGN KEY (`status_code`)
    REFERENCES `test`.`Rent_Status` (`rental_status_code`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
mysql foreign-key

1 个回答

  1. Best Answer

    似乎外键选项不能未定义！

    CONSTRAINT `fk_Rent_Status_Code`
    FOREIGN KEY (`status_code`)
    REFERENCES `test`.`Rent_Status` (`rental_status_code`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

    必须改为：

    CONSTRAINT `fk_Rent_Status_Code`
    FOREIGN KEY (`status_code`)
    REFERENCES `test`.`Rent_Status` (`rental_status_code`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
    • 0

相关问题