Mysql

MySQL WorkBench SQL：創建數據庫時無法添加外鍵約束（錯誤 1215）

  • June 25, 2018

問題：

我正在接收

錯誤 1215：無法添加外鍵約束

執行從我用來為數據庫建模的 EER 圖生成的 MySQL 模式程式碼時出錯。

添加表時發生錯誤Person（我懷疑表消息也可能給出相同的問題）

尋找解決方案：

總體而言，最可能出現的問題是缺少索引了多個列的父（標識）實體的索引。

請參閱 1) 2) - 1215 錯誤的可能原因

在 DBA Stackexchange 上搜尋，簡單的人為錯誤導致的問題，似乎與我的問題無關： -發布 -另一個文章

請參閱下面的完整數據庫 SQL：（您可以將其導入 MySQL Workbench 以查看實體模型）

-- MySQL Script generated by MySQL Workbench
-- Sun Jun 24 23:40:56 2018
-- 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 rech_system
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema rech_system
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `rech_system` DEFAULT CHARACTER SET utf8 ;
USE `rech_system` ;

-- -----------------------------------------------------
-- Table `rech_system`.`Faculty`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`Faculty` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`Faculty` (
 `faculty_name` VARCHAR(50) NOT NULL,
 `faculty_info` TEXT NULL,
 PRIMARY KEY (`faculty_name`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `rech_system`.`Department`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`Department` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`Department` (
 `department_name` VARCHAR(50) NOT NULL,
 `faculty_name` VARCHAR(50) NOT NULL,
 PRIMARY KEY (`department_name`, `faculty_name`),
 CONSTRAINT `fk_Department_Faculty`
   FOREIGN KEY (`faculty_name`)
   REFERENCES `rech_system`.`Faculty` (`faculty_name`)
   ON DELETE CASCADE
   ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_Department_Faculty_idx` ON `rech_system`.`Department` (`faculty_name` ASC);


-- -----------------------------------------------------
-- Table `rech_system`.`Person`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`Person` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`Person` (
 `user_email` VARCHAR(100) NOT NULL,
 `user_password_hash` VARCHAR(50) NOT NULL,
 `user_firstname` VARCHAR(50) NULL,
 `user_lastname` VARCHAR(50) NULL,
 `user_gender` CHAR(1) NULL,
 `current_degree_level` VARCHAR(20) NULL,
 `contact_number_mobile` VARCHAR(15) NULL,
 `person_type` VARCHAR(10) NULL,
 `contact_office_telephone` VARCHAR(15) NULL,
 `office_address` VARCHAR(40) NULL,
 `faculty_name` VARCHAR(50) NULL,
 `department_name` VARCHAR(50) NULL,
 `Faculty_faculty_name` VARCHAR(50) NOT NULL,
 `Department_department_name` VARCHAR(50) NOT NULL,
 `Department_faculty_name` VARCHAR(50) NOT NULL,
 PRIMARY KEY (`user_email`, `Department_faculty_name`, `Department_department_name`, `Faculty_faculty_name`),
 CONSTRAINT `fk_Faculty`
   FOREIGN KEY (`Faculty_faculty_name`)
   REFERENCES `rech_system`.`Faculty` (`faculty_name`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_Department`
   FOREIGN KEY (`Department_department_name` , `Department_faculty_name`)
   REFERENCES `rech_system`.`Department` (`department_name` , `faculty_name`)
   ON DELETE CASCADE
   ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE UNIQUE INDEX `user_email_UNIQUE` ON `rech_system`.`Person` (`user_email` ASC);

CREATE INDEX `fk_Person_Faculty1_idx` ON `rech_system`.`Person` (`Faculty_faculty_name` ASC);

CREATE INDEX `fk_Person_Department1_idx` ON `rech_system`.`Person` (`Department_department_name` ASC, `Department_faculty_name` ASC);


-- -----------------------------------------------------
-- Table `rech_system`.`Ethics_Application`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`Ethics_Application` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`Ethics_Application` (
 `application_type` CHAR(1) NOT NULL,
 `application_year` YEAR NOT NULL,
 `application_department` VARCHAR(50) NOT NULL,
 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
 `is_submitted` TINYINT NULL DEFAULT '0',
 `date_submitted` VARCHAR(45) NULL DEFAULT NULL,
 `date_approved` VARCHAR(45) NULL DEFAULT NULL,
 `pi_id` VARCHAR(100) NOT NULL,
 `pi_approved_date` VARCHAR(45) NULL DEFAULT NULL,
 `prp_id` VARCHAR(100) NOT NULL,
 `prp_approved_date` VARCHAR(45) NULL DEFAULT NULL,
 `hod_id` VARCHAR(100) NULL DEFAULT NULL,
 `hod_approved` VARCHAR(45) NULL DEFAULT NULL,
 `rti_id` VARCHAR(100) NULL DEFAULT NULL,
 `rti_approved` VARCHAR(45) NULL DEFAULT NULL,
 `liaison_id` VARCHAR(100) NULL DEFAULT NULL,
 PRIMARY KEY (`application_type`, `application_year`, `application_department`, `application_number`),
 CONSTRAINT `fk_Person_user_email_pi`
   FOREIGN KEY (`pi_id`)
   REFERENCES `rech_system`.`Person` (`user_email`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_Person_user_email_prp`
   FOREIGN KEY (`prp_id`)
   REFERENCES `rech_system`.`Person` (`user_email`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_Person_user_email_rti`
   FOREIGN KEY (`rti_id`)
   REFERENCES `rech_system`.`Person` (`user_email`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_Person_user_email_hod`
   FOREIGN KEY (`hod_id`)
   REFERENCES `rech_system`.`Person` (`user_email`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_Person_user_email_liaison`
   FOREIGN KEY (`liaison_id`)
   REFERENCES `rech_system`.`Person` (`user_email`)
   ON DELETE CASCADE
   ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE INDEX `fk_Person_user_email_pi_idx` ON `rech_system`.`Ethics_Application` (`pi_id` ASC);

CREATE INDEX `fk_Person_user_email_prp_idx` ON `rech_system`.`Ethics_Application` (`prp_id` ASC);

CREATE INDEX `fk_Person_user_email_rti_idx` ON `rech_system`.`Ethics_Application` (`rti_id` ASC);

CREATE INDEX `fk_Person_user_email_hod_idx` ON `rech_system`.`Ethics_Application` (`hod_id` ASC);

CREATE INDEX `fk_Person_user_email_liaison_idx` ON `rech_system`.`Ethics_Application` (`liaison_id` ASC);


-- -----------------------------------------------------
-- Table `rech_system`.`Message`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`Message` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`Message` (
 `message_date` VARCHAR(45) NOT NULL,
 `message` TEXT NULL DEFAULT NULL,
 `user_email_sender` VARCHAR(100) NOT NULL,
 `application_type` CHAR(1) NOT NULL,
 `application_year` YEAR NOT NULL,
 `application_department` VARCHAR(50) NOT NULL,
 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
 `user_email_receiver` VARCHAR(100) NOT NULL,
 PRIMARY KEY (`message_date`, `application_number`, `application_department`, `application_year`, `application_type`, `user_email_sender`, `user_email_receiver`),
 CONSTRAINT `fk_Person_Sender`
   FOREIGN KEY (`user_email_sender`)
   REFERENCES `rech_system`.`Person` (`user_email`)
   ON DELETE RESTRICT
   ON UPDATE CASCADE,
 CONSTRAINT `fk_Ethics_Application`
   FOREIGN KEY (`application_type` , `application_year` , `application_department` , `application_number`)
   REFERENCES `rech_system`.`Ethics_Application` (`application_type` , `application_year` , `application_department` , `application_number`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_Person_Receiver`
   FOREIGN KEY (`user_email_receiver`)
   REFERENCES `rech_system`.`Person` (`user_email`)
   ON DELETE CASCADE
   ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE INDEX `application_idx` ON `rech_system`.`Message` ();

CREATE INDEX `fk_Person_Sender_idx` ON `rech_system`.`Message` (`user_email_sender` ASC);

CREATE INDEX `fk_Ethics_Application_idx` ON `rech_system`.`Message` (`application_type` ASC, `application_year` ASC, `application_department` ASC, `application_number` ASC);

CREATE INDEX `fk_Person_Receiver_idx` ON `rech_system`.`Message` (`user_email_receiver` ASC);


-- -----------------------------------------------------
-- Table `rech_system`.`Meeting`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`Meeting` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`Meeting` (
 `meeting_date` DATETIME NOT NULL,
 PRIMARY KEY (`meeting_date`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `rech_system`.`AgendaItem`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`AgendaItem` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`AgendaItem` (
 `resolution` TEXT NULL,
 `application_status` TINYINT NULL,
 `application_type` CHAR(1) NOT NULL,
 `application_year` YEAR NOT NULL,
 `application_department` VARCHAR(50) NOT NULL,
 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
 `meeting_date` DATETIME NOT NULL,
 PRIMARY KEY (`application_number`, `application_department`, `application_year`, `application_type`, `meeting_date`),
 CONSTRAINT `fk_Ethics_Application`
   FOREIGN KEY (`application_type` , `application_year` , `application_department` , `application_number`)
   REFERENCES `rech_system`.`Ethics_Application` (`application_type` , `application_year` , `application_department` , `application_number`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_Meeting`
   FOREIGN KEY (`meeting_date`)
   REFERENCES `rech_system`.`Meeting` (`meeting_date`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_Ethics_Application_idx` ON `rech_system`.`AgendaItem` (`application_type` ASC, `application_year` ASC, `application_department` ASC, `application_number` ASC);

CREATE INDEX `fk_Meeting_idx` ON `rech_system`.`AgendaItem` (`meeting_date` ASC);


-- -----------------------------------------------------
-- Table `rech_system`.`Component`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`Component` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`Component` (
 `section_id` SMALLINT(2) NOT NULL,
 `component_id` SMALLINT(2) NOT NULL,
 `question` VARCHAR(255) NULL,
 `application_type` CHAR(1) NOT NULL,
 `application_year` YEAR NOT NULL,
 `application_department` VARCHAR(50) NOT NULL,
 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
 PRIMARY KEY (`section_id`, `component_id`, `application_type`, `application_year`, `application_department`, `application_number`),
 CONSTRAINT `fk_Ethics_Application`
   FOREIGN KEY (`application_type` , `application_year` , `application_department` , `application_number`)
   REFERENCES `rech_system`.`Ethics_Application` (`application_type` , `application_year` , `application_department` , `application_number`)
   ON DELETE CASCADE
   ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_Component_Ethics_Application_idx` ON `rech_system`.`Component` (`application_type` ASC, `application_year` ASC, `application_department` ASC, `application_number` ASC);


-- -----------------------------------------------------
-- Table `rech_system`.`ComponentVersion`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`ComponentVersion` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`ComponentVersion` (
 `version` TINYINT NOT NULL AUTO_INCREMENT,
 `is_submitted` TINYINT NULL,
 `date_submitted` DATETIME NULL,
 `date_last_edited` DATETIME NULL,
 `response_type` VARCHAR(8) NULL,
 `text_value` TEXT NULL,
 `bool_value` TINYINT NULL,
 `document_name` VARCHAR(100) NULL,
 `document_description` VARCHAR(255) NULL,
 `document_blob` BLOB NULL,
 `section_id` SMALLINT(2) NOT NULL,
 `component_id` SMALLINT(2) NOT NULL,
 `application_type` CHAR(1) NOT NULL,
 `application_year` YEAR NOT NULL,
 `application_department` VARCHAR(50) NOT NULL,
 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
 PRIMARY KEY (`version`, `section_id`, `component_id`, `application_type`, `application_year`, `application_department`, `application_number`),
 CONSTRAINT `fk_ComponentVersion_Component`
   FOREIGN KEY (`section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
   REFERENCES `rech_system`.`Component` (`section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
   ON DELETE CASCADE
   ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_Component_idx` ON `rech_system`.`ComponentVersion` (`section_id` ASC, `component_id` ASC, `application_type` ASC, `application_year` ASC, `application_department` ASC, `application_number` ASC);

CREATE INDEX `fk_Application_idx` ON `rech_system`.`ComponentVersion` (`application_number` ASC, `application_department` ASC, `application_year` ASC, `application_type` ASC);


-- -----------------------------------------------------
-- Table `rech_system`.`ReviewerFeedback`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`ReviewerFeedback` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`ReviewerFeedback` (
 `feedback_date` DATETIME NULL,
 `application_assigned_date` DATETIME NULL,
 `user_email` VARCHAR(100) NOT NULL,
 `version` TINYINT NOT NULL,
 `section_id` SMALLINT(2) NOT NULL,
 `component_id` SMALLINT(2) NOT NULL,
 `application_type` CHAR(1) NOT NULL,
 `application_year` YEAR NOT NULL,
 `application_department` VARCHAR(50) NOT NULL,
 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
 PRIMARY KEY (`application_number`, `application_department`, `application_year`, `application_type`, `component_id`, `section_id`, `version`, `user_email`),
 CONSTRAINT `fk_ReviewerFeedback_Person`
   FOREIGN KEY (`user_email`)
   REFERENCES `rech_system`.`Person` (`user_email`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_ReviewerFeedback_ComponentVersion`
   FOREIGN KEY (`version` , `section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
   REFERENCES `rech_system`.`ComponentVersion` (`version` , `section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
   ON DELETE CASCADE
   ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_Reviewer_idx` ON `rech_system`.`ReviewerFeedback` (`user_email` ASC);

CREATE INDEX `fk_ComponentVersion_idx` ON `rech_system`.`ReviewerFeedback` (`version` ASC, `section_id` ASC, `component_id` ASC, `application_type` ASC, `application_year` ASC, `application_department` ASC, `application_number` ASC);


-- -----------------------------------------------------
-- Table `rech_system`.`ReviewerComponentFeedback`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`ReviewerComponentFeedback` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`ReviewerComponentFeedback` (
 `component_feedback` VARCHAR(255) NULL,
 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
 `application_department` VARCHAR(50) NOT NULL,
 `application_year` YEAR NOT NULL,
 `application_type` CHAR(1) NOT NULL,
 `component_id` SMALLINT(2) NOT NULL,
 `section_id` SMALLINT(2) NOT NULL,
 `version` TINYINT NOT NULL,
 `user_email` VARCHAR(100) NOT NULL,
 `ComponentVersion_version` TINYINT NOT NULL,
 `ComponentVersion_section_id` SMALLINT(2) NOT NULL,
 `ComponentVersion_component_id` SMALLINT(2) NOT NULL,
 `ComponentVersion_application_type` CHAR(1) NOT NULL,
 `ComponentVersion_application_year` YEAR NOT NULL,
 `ComponentVersion_application_department` VARCHAR(50) NOT NULL,
 `ComponentVersion_application_number` SMALLINT(5) UNSIGNED NOT NULL,
 PRIMARY KEY (`application_number`, `application_department`, `application_year`, `application_type`, `component_id`, `section_id`, `version`, `user_email`, `ComponentVersion_version`, `ComponentVersion_section_id`, `ComponentVersion_component_id`, `ComponentVersion_application_type`, `ComponentVersion_application_year`, `ComponentVersion_application_department`, `ComponentVersion_application_number`),
 CONSTRAINT `fk_ReviewerFeedback`
   FOREIGN KEY (`application_number` , `application_department` , `application_year` , `application_type` , `component_id` , `section_id` , `version` , `user_email`)
   REFERENCES `rech_system`.`ReviewerFeedback` (`application_number` , `application_department` , `application_year` , `application_type` , `component_id` , `section_id` , `version` , `user_email`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_ComponentVersion`
   FOREIGN KEY (`ComponentVersion_version` , `ComponentVersion_section_id` , `ComponentVersion_component_id` , `ComponentVersion_application_type` , `ComponentVersion_application_year` , `ComponentVersion_application_department` , `ComponentVersion_application_number`)
   REFERENCES `rech_system`.`ComponentVersion` (`version` , `section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
   ON DELETE CASCADE
   ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_ComponentVersion_idx` ON `rech_system`.`ReviewerComponentFeedback` (`ComponentVersion_version` ASC, `ComponentVersion_section_id` ASC, `ComponentVersion_component_id` ASC, `ComponentVersion_application_type` ASC, `ComponentVersion_application_year` ASC, `ComponentVersion_application_department` ASC, `ComponentVersion_application_number` ASC);


-- -----------------------------------------------------
-- Table `rech_system`.`LiaisonFeedback`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`LiaisonFeedback` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`LiaisonFeedback` (
 `feedback_date` DATETIME NOT NULL,
 `application_assigned_date` DATETIME NULL,
 `user_email` VARCHAR(100) NOT NULL,
 `version` TINYINT NOT NULL,
 `section_id` SMALLINT(2) NOT NULL,
 `component_id` SMALLINT(2) NOT NULL,
 `application_type` CHAR(1) NOT NULL,
 `application_year` YEAR NOT NULL,
 `application_department` VARCHAR(50) NOT NULL,
 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
 PRIMARY KEY (`feedback_date`, `application_number`, `application_department`, `application_year`, `application_type`, `component_id`, `section_id`, `version`, `user_email`),
 CONSTRAINT `fk_LiaisonFeedback_Person`
   FOREIGN KEY (`user_email`)
   REFERENCES `rech_system`.`Person` (`user_email`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_LiaisonFeedback_ComponentVersion`
   FOREIGN KEY (`version` , `section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
   REFERENCES `rech_system`.`ComponentVersion` (`version` , `section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
   ON DELETE CASCADE
   ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_Liaison_idx` ON `rech_system`.`LiaisonFeedback` (`user_email` ASC);

CREATE INDEX `fk_ComponenVersion_idx` ON `rech_system`.`LiaisonFeedback` (`version` ASC, `section_id` ASC, `component_id` ASC, `application_type` ASC, `application_year` ASC, `application_department` ASC, `application_number` ASC);


-- -----------------------------------------------------
-- Table `rech_system`.`LiaisonComponentFeedback`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`LiaisonComponentFeedback` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`LiaisonComponentFeedback` (
 `component_feedback` VARCHAR(255) NULL,
 `feedback_date` DATETIME NOT NULL,
 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
 `application_department` VARCHAR(50) NOT NULL,
 `application_year` YEAR NOT NULL,
 `application_type` CHAR(1) NOT NULL,
 `component_id` SMALLINT(2) NOT NULL,
 `section_id` SMALLINT(2) NOT NULL,
 `version` TINYINT NOT NULL,
 `user_email` VARCHAR(100) NOT NULL,
 `Component_version` TINYINT NOT NULL,
 `Component_section_id` SMALLINT(2) NOT NULL,
 `ComponentVersion_component_id` SMALLINT(2) NOT NULL,
 `ComponentVersion_application_type` CHAR(1) NOT NULL,
 `ComponentVersion_application_year` YEAR NOT NULL,
 `ComponentVersion_application_department` VARCHAR(50) NOT NULL,
 `ComponentVersion_application_number` SMALLINT(5) UNSIGNED NOT NULL,
 PRIMARY KEY (`feedback_date`, `application_number`, `application_department`, `application_year`, `application_type`, `component_id`, `section_id`, `version`, `user_email`, `Component_version`, `Component_section_id`, `ComponentVersion_component_id`, `ComponentVersion_application_type`, `ComponentVersion_application_year`, `ComponentVersion_application_department`, `ComponentVersion_application_number`),
 CONSTRAINT `fk_LiaisonFeedback_idx`
   FOREIGN KEY (`feedback_date` , `application_number` , `application_department` , `application_year` , `application_type` , `component_id` , `section_id` , `version` , `user_email`)
   REFERENCES `rech_system`.`LiaisonFeedback` (`feedback_date` , `application_number` , `application_department` , `application_year` , `application_type` , `component_id` , `section_id` , `version` , `user_email`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_ComponentVersion`
   FOREIGN KEY (`Component_version` , `Component_section_id` , `ComponentVersion_component_id` , `ComponentVersion_application_type` , `ComponentVersion_application_year` , `ComponentVersion_application_department` , `ComponentVersion_application_number`)
   REFERENCES `rech_system`.`ComponentVersion` (`version` , `section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
   ON DELETE CASCADE
   ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_LiaisonFeedback_idx` ON `rech_system`.`LiaisonComponentFeedback` (`feedback_date` ASC, `application_number` ASC, `application_department` ASC, `application_year` ASC, `application_type` ASC, `component_id` ASC, `section_id` ASC, `version` ASC, `user_email` ASC);

CREATE INDEX `fk_ComponentVersionFeedback_idx` ON `rech_system`.`LiaisonComponentFeedback` (`Component_version` ASC, `Component_section_id` ASC, `ComponentVersion_component_id` ASC, `ComponentVersion_application_type` ASC, `ComponentVersion_application_year` ASC, `ComponentVersion_application_department` ASC, `ComponentVersion_application_number` ASC);


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

我不知道是什麼導致了問題，為什麼外鍵約束有問題。

建議將不勝感激！

我無法導入 ER 圖。似乎外鍵約束在多個表中具有相同的名稱，只是重命名了衝突的約束名稱。

   -- MySQL Script generated by MySQL Workbench
   -- Sun Jun 24 23:40:56 2018
   -- 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 rech_system
   -- -----------------------------------------------------

   -- -----------------------------------------------------
   -- Schema rech_system
   -- -----------------------------------------------------
   CREATE SCHEMA IF NOT EXISTS `rech_system` DEFAULT CHARACTER SET utf8 ;
   USE `rech_system` ;

   -- -----------------------------------------------------
   -- Table `rech_system`.`Faculty`
   -- -----------------------------------------------------
   DROP TABLE IF EXISTS `rech_system`.`Faculty` ;

   CREATE TABLE IF NOT EXISTS `rech_system`.`Faculty` (
   `faculty_name` VARCHAR(50) NOT NULL,
   `faculty_info` TEXT NULL,
   PRIMARY KEY (`faculty_name`))
   ENGINE = InnoDB;

    -- -----------------------------------------------------
   -- Table `rech_system`.`Department`
   -- -----------------------------------------------------
   DROP TABLE IF EXISTS `rech_system`.`Department` ;

   CREATE TABLE IF NOT EXISTS `rech_system`.`Department` (
     `department_name` VARCHAR(50) NOT NULL,
    `faculty_name` VARCHAR(50) NOT NULL,
     PRIMARY KEY (`department_name`, `faculty_name`),
     CONSTRAINT `fk_Department_Faculty`
   FOREIGN KEY (`faculty_name`)
   REFERENCES `rech_system`.`Faculty` (`faculty_name`)
   ON DELETE CASCADE
   ON UPDATE CASCADE)
   ENGINE = InnoDB;

  CREATE INDEX `fk_Department_Faculty_idx` ON `rech_system`.`Department`    (`faculty_name` ASC);


-- -----------------------------------------------------
-- Table `rech_system`.`Person`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`Person` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`Person` (
 `user_email` VARCHAR(100) NOT NULL,
 `user_password_hash` VARCHAR(50) NOT NULL,
 `user_firstname` VARCHAR(50) NULL,
 `user_lastname` VARCHAR(50) NULL,
 `user_gender` CHAR(1) NULL,
 `current_degree_level` VARCHAR(20) NULL,
 `contact_number_mobile` VARCHAR(15) NULL,
 `person_type` VARCHAR(10) NULL,
 `contact_office_telephone` VARCHAR(15) NULL,
 `office_address` VARCHAR(40) NULL,
 `faculty_name` VARCHAR(50) NULL,
 `department_name` VARCHAR(50) NULL,
 `Faculty_faculty_name` VARCHAR(50) NOT NULL,
 `Department_department_name` VARCHAR(50) NOT NULL,
 `Department_faculty_name` VARCHAR(50) NOT NULL,
 PRIMARY KEY (`user_email`, `Department_faculty_name`, `Department_department_name`, `Faculty_faculty_name`),
 CONSTRAINT `fk_Faculty`
   FOREIGN KEY (`Faculty_faculty_name`)
   REFERENCES `rech_system`.`Faculty` (`faculty_name`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_Department`
   FOREIGN KEY (`Department_department_name` , `Department_faculty_name`)
   REFERENCES `rech_system`.`Department` (`department_name` , `faculty_name`)
   ON DELETE CASCADE
   ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE UNIQUE INDEX `user_email_UNIQUE` ON `rech_system`.`Person` (`user_email` ASC);

CREATE INDEX `fk_Person_Faculty1_idx` ON `rech_system`.`Person` (`Faculty_faculty_name` ASC);

CREATE INDEX `fk_Person_Department1_idx` ON `rech_system`.`Person` (`Department_department_name` ASC, `Department_faculty_name` ASC);


-- -----------------------------------------------------
-- Table `rech_system`.`Ethics_Application`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`Ethics_Application` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`Ethics_Application` (
 `application_type` CHAR(1) NOT NULL,
 `application_year` YEAR NOT NULL,
 `application_department` VARCHAR(50) NOT NULL,
 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
 `is_submitted` TINYINT NULL DEFAULT '0',
 `date_submitted` VARCHAR(45) NULL DEFAULT NULL,
 `date_approved` VARCHAR(45) NULL DEFAULT NULL,
 `pi_id` VARCHAR(100) NOT NULL,
 `pi_approved_date` VARCHAR(45) NULL DEFAULT NULL,
 `prp_id` VARCHAR(100) NOT NULL,
 `prp_approved_date` VARCHAR(45) NULL DEFAULT NULL,
 `hod_id` VARCHAR(100) NULL DEFAULT NULL,
 `hod_approved` VARCHAR(45) NULL DEFAULT NULL,
 `rti_id` VARCHAR(100) NULL DEFAULT NULL,
 `rti_approved` VARCHAR(45) NULL DEFAULT NULL,
 `liaison_id` VARCHAR(100) NULL DEFAULT NULL,
 PRIMARY KEY (`application_type`, `application_year`, `application_department`, `application_number`),
 CONSTRAINT `fk_Person_user_email_pi`
   FOREIGN KEY (`pi_id`)
   REFERENCES `rech_system`.`Person` (`user_email`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_Person_user_email_prp`
   FOREIGN KEY (`prp_id`)
   REFERENCES `rech_system`.`Person` (`user_email`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_Person_user_email_rti`
   FOREIGN KEY (`rti_id`)
   REFERENCES `rech_system`.`Person` (`user_email`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_Person_user_email_hod`
   FOREIGN KEY (`hod_id`)
   REFERENCES `rech_system`.`Person` (`user_email`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_Person_user_email_liaison`
   FOREIGN KEY (`liaison_id`)
   REFERENCES `rech_system`.`Person` (`user_email`)
   ON DELETE CASCADE
   ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE INDEX `fk_Person_user_email_pi_idx` ON `rech_system`.`Ethics_Application` (`pi_id` ASC);

CREATE INDEX `fk_Person_user_email_prp_idx` ON `rech_system`.`Ethics_Application` (`prp_id` ASC);

CREATE INDEX `fk_Person_user_email_rti_idx` ON `rech_system`.`Ethics_Application` (`rti_id` ASC);

CREATE INDEX `fk_Person_user_email_hod_idx` ON `rech_system`.`Ethics_Application` (`hod_id` ASC);

CREATE INDEX `fk_Person_user_email_liaison_idx` ON `rech_system`.`Ethics_Application` (`liaison_id` ASC);


-- -----------------------------------------------------
-- Table `rech_system`.`Message`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`Message` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`Message` (
 `message_date` VARCHAR(45) NOT NULL,
 `message` TEXT NULL DEFAULT NULL,
 `user_email_sender` VARCHAR(100) NOT NULL,
 `application_type` CHAR(1) NOT NULL,
 `application_year` YEAR NOT NULL,
 `application_department` VARCHAR(50) NOT NULL,
 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
 `user_email_receiver` VARCHAR(100) NOT NULL,
 PRIMARY KEY (`message_date`, `application_number`, `application_department`, `application_year`, `application_type`, `user_email_sender`, `user_email_receiver`),
 CONSTRAINT `fk_Person_Sender`
   FOREIGN KEY (`user_email_sender`)
   REFERENCES `rech_system`.`Person` (`user_email`)
   ON DELETE RESTRICT
   ON UPDATE CASCADE,
 CONSTRAINT `fk_Ethics_Application`
   FOREIGN KEY (`application_type` , `application_year` , `application_department` , `application_number`)
   REFERENCES `rech_system`.`Ethics_Application` (`application_type` , `application_year` , `application_department` , `application_number`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_Person_Receiver`
   FOREIGN KEY (`user_email_receiver`)
   REFERENCES `rech_system`.`Person` (`user_email`)
   ON DELETE CASCADE
   ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE INDEX `application_idx` ON `rech_system`.`Message` ();

CREATE INDEX `fk_Person_Sender_idx` ON `rech_system`.`Message` (`user_email_sender` ASC);

CREATE INDEX `fk_Ethics_Application_idx` ON `rech_system`.`Message` (`application_type` ASC, `application_year` ASC, `application_department` ASC, `application_number` ASC);

CREATE INDEX `fk_Person_Receiver_idx` ON `rech_system`.`Message` (`user_email_receiver` ASC);


-- -----------------------------------------------------
-- Table `rech_system`.`Meeting`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`Meeting` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`Meeting` (
 `meeting_date` DATETIME NOT NULL,
 PRIMARY KEY (`meeting_date`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `rech_system`.`AgendaItem`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`AgendaItem` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`AgendaItem` (
 `resolution` TEXT NULL,
 `application_status` TINYINT NULL,
 `application_type` CHAR(1) NOT NULL,
 `application_year` YEAR NOT NULL,
 `application_department` VARCHAR(50) NOT NULL,
 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
 `meeting_date` DATETIME NOT NULL,
 PRIMARY KEY (`application_number`, `application_department`, `application_year`, `application_type`, `meeting_date`),
 CONSTRAINT `fk_Ethics_Application_AgendaItem`
   FOREIGN KEY (`application_type` , `application_year` , `application_department` , `application_number`)
   REFERENCES `rech_system`.`Ethics_Application` (`application_type` , `application_year` , `application_department` , `application_number`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_Meeting`
   FOREIGN KEY (`meeting_date`)
   REFERENCES `rech_system`.`Meeting` (`meeting_date`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_Ethics_Application_idx` ON `rech_system`.`AgendaItem` (`application_type` ASC, `application_year` ASC, `application_department` ASC, `application_number` ASC);

CREATE INDEX `fk_Meeting_idx` ON `rech_system`.`AgendaItem` (`meeting_date` ASC);


-- -----------------------------------------------------
-- Table `rech_system`.`Component`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`Component` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`Component` (
 `section_id` SMALLINT(2) NOT NULL,
 `component_id` SMALLINT(2) NOT NULL,
 `question` VARCHAR(255) NULL,
 `application_type` CHAR(1) NOT NULL,
 `application_year` YEAR NOT NULL,
 `application_department` VARCHAR(50) NOT NULL,
 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
 PRIMARY KEY (`section_id`, `component_id`, `application_type`, `application_year`, `application_department`, `application_number`),
 CONSTRAINT `fk_Ethics_Application_component`
   FOREIGN KEY (`application_type` , `application_year` , `application_department` , `application_number`)
   REFERENCES `rech_system`.`Ethics_Application` (`application_type` , `application_year` , `application_department` , `application_number`)
   ON DELETE CASCADE
   ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_Component_Ethics_Application_idx` ON `rech_system`.`Component` (`application_type` ASC, `application_year` ASC, `application_department` ASC, `application_number` ASC);


-- -----------------------------------------------------
-- Table `rech_system`.`ComponentVersion`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`ComponentVersion` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`ComponentVersion` (
 `version` TINYINT NOT NULL AUTO_INCREMENT,
 `is_submitted` TINYINT NULL,
 `date_submitted` DATETIME NULL,
 `date_last_edited` DATETIME NULL,
 `response_type` VARCHAR(8) NULL,
 `text_value` TEXT NULL,
 `bool_value` TINYINT NULL,
 `document_name` VARCHAR(100) NULL,
 `document_description` VARCHAR(255) NULL,
 `document_blob` BLOB NULL,
 `section_id` SMALLINT(2) NOT NULL,
 `component_id` SMALLINT(2) NOT NULL,
 `application_type` CHAR(1) NOT NULL,
 `application_year` YEAR NOT NULL,
 `application_department` VARCHAR(50) NOT NULL,
 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
 PRIMARY KEY (`version`, `section_id`, `component_id`, `application_type`, `application_year`, `application_department`, `application_number`),
 CONSTRAINT `fk_ComponentVersion_Component`
   FOREIGN KEY (`section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
   REFERENCES `rech_system`.`Component` (`section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
   ON DELETE CASCADE
   ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_Component_idx` ON `rech_system`.`ComponentVersion` (`section_id` ASC, `component_id` ASC, `application_type` ASC, `application_year` ASC, `application_department` ASC, `application_number` ASC);

CREATE INDEX `fk_Application_idx` ON `rech_system`.`ComponentVersion` (`application_number` ASC, `application_department` ASC, `application_year` ASC, `application_type` ASC);


-- -----------------------------------------------------
-- Table `rech_system`.`ReviewerFeedback`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`ReviewerFeedback` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`ReviewerFeedback` (
 `feedback_date` DATETIME NULL,
 `application_assigned_date` DATETIME NULL,
 `user_email` VARCHAR(100) NOT NULL,
 `version` TINYINT NOT NULL,
 `section_id` SMALLINT(2) NOT NULL,
 `component_id` SMALLINT(2) NOT NULL,
 `application_type` CHAR(1) NOT NULL,
 `application_year` YEAR NOT NULL,
 `application_department` VARCHAR(50) NOT NULL,
 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
 PRIMARY KEY (`application_number`, `application_department`, `application_year`, `application_type`, `component_id`, `section_id`, `version`, `user_email`),
 CONSTRAINT `fk_ReviewerFeedback_Person`
   FOREIGN KEY (`user_email`)
   REFERENCES `rech_system`.`Person` (`user_email`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_ReviewerFeedback_ComponentVersion`
   FOREIGN KEY (`version` , `section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
   REFERENCES `rech_system`.`ComponentVersion` (`version` , `section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
   ON DELETE CASCADE
   ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_Reviewer_idx` ON `rech_system`.`ReviewerFeedback` (`user_email` ASC);

CREATE INDEX `fk_ComponentVersion_idx` ON `rech_system`.`ReviewerFeedback` (`version` ASC, `section_id` ASC, `component_id` ASC, `application_type` ASC, `application_year` ASC, `application_department` ASC, `application_number` ASC);


-- -----------------------------------------------------
-- Table `rech_system`.`ReviewerComponentFeedback`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`ReviewerComponentFeedback` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`ReviewerComponentFeedback` (
 `component_feedback` VARCHAR(255) NULL,
 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
 `application_department` VARCHAR(50) NOT NULL,
 `application_year` YEAR NOT NULL,
 `application_type` CHAR(1) NOT NULL,
 `component_id` SMALLINT(2) NOT NULL,
 `section_id` SMALLINT(2) NOT NULL,
 `version` TINYINT NOT NULL,
 `user_email` VARCHAR(100) NOT NULL,
 `ComponentVersion_version` TINYINT NOT NULL,
 `ComponentVersion_section_id` SMALLINT(2) NOT NULL,
 `ComponentVersion_component_id` SMALLINT(2) NOT NULL,
 `ComponentVersion_application_type` CHAR(1) NOT NULL,
 `ComponentVersion_application_year` YEAR NOT NULL,
 `ComponentVersion_application_department` VARCHAR(50) NOT NULL,
 `ComponentVersion_application_number` SMALLINT(5) UNSIGNED NOT NULL,
 PRIMARY KEY (`application_number`, `application_department`, `application_year`, `application_type`, `component_id`, `section_id`, `version`, `user_email`, `ComponentVersion_version`, `ComponentVersion_section_id`, `ComponentVersion_component_id`, `ComponentVersion_application_type`, `ComponentVersion_application_year`, `ComponentVersion_application_department`, `ComponentVersion_application_number`),
 CONSTRAINT `fk_ReviewerFeedback`
   FOREIGN KEY (`application_number` , `application_department` , `application_year` , `application_type` , `component_id` , `section_id` , `version` , `user_email`)
   REFERENCES `rech_system`.`ReviewerFeedback` (`application_number` , `application_department` , `application_year` , `application_type` , `component_id` , `section_id` , `version` , `user_email`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_ComponentVersion`
   FOREIGN KEY (`ComponentVersion_version` , `ComponentVersion_section_id` , `ComponentVersion_component_id` , `ComponentVersion_application_type` , `ComponentVersion_application_year` , `ComponentVersion_application_department` , `ComponentVersion_application_number`)
   REFERENCES `rech_system`.`ComponentVersion` (`version` , `section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
   ON DELETE CASCADE
   ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_ComponentVersion_idx` ON `rech_system`.`ReviewerComponentFeedback` (`ComponentVersion_version` ASC, `ComponentVersion_section_id` ASC, `ComponentVersion_component_id` ASC, `ComponentVersion_application_type` ASC, `ComponentVersion_application_year` ASC, `ComponentVersion_application_department` ASC, `ComponentVersion_application_number` ASC);


-- -----------------------------------------------------
-- Table `rech_system`.`LiaisonFeedback`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`LiaisonFeedback` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`LiaisonFeedback` (
 `feedback_date` DATETIME NOT NULL,
 `application_assigned_date` DATETIME NULL,
 `user_email` VARCHAR(100) NOT NULL,
 `version` TINYINT NOT NULL,
 `section_id` SMALLINT(2) NOT NULL,
 `component_id` SMALLINT(2) NOT NULL,
 `application_type` CHAR(1) NOT NULL,
 `application_year` YEAR NOT NULL,
 `application_department` VARCHAR(50) NOT NULL,
 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
 PRIMARY KEY (`feedback_date`, `application_number`, `application_department`, `application_year`, `application_type`, `component_id`, `section_id`, `version`, `user_email`),
 CONSTRAINT `fk_LiaisonFeedback_Person`
   FOREIGN KEY (`user_email`)
   REFERENCES `rech_system`.`Person` (`user_email`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_LiaisonFeedback_ComponentVersion_Liason`
   FOREIGN KEY (`version` , `section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
   REFERENCES `rech_system`.`ComponentVersion` (`version` , `section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
   ON DELETE CASCADE
   ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_Liaison_idx` ON `rech_system`.`LiaisonFeedback` (`user_email` ASC);

CREATE INDEX `fk_ComponenVersion_idx` ON `rech_system`.`LiaisonFeedback` (`version` ASC, `section_id` ASC, `component_id` ASC, `application_type` ASC, `application_year` ASC, `application_department` ASC, `application_number` ASC);


-- -----------------------------------------------------
-- Table `rech_system`.`LiaisonComponentFeedback`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `rech_system`.`LiaisonComponentFeedback` ;

CREATE TABLE IF NOT EXISTS `rech_system`.`LiaisonComponentFeedback` (
 `component_feedback` VARCHAR(255) NULL,
 `feedback_date` DATETIME NOT NULL,
 `application_number` SMALLINT(5) UNSIGNED NOT NULL,
 `application_department` VARCHAR(50) NOT NULL,
 `application_year` YEAR NOT NULL,
 `application_type` CHAR(1) NOT NULL,
 `component_id` SMALLINT(2) NOT NULL,
 `section_id` SMALLINT(2) NOT NULL,
 `version` TINYINT NOT NULL,
 `user_email` VARCHAR(100) NOT NULL,
 `Component_version` TINYINT NOT NULL,
 `Component_section_id` SMALLINT(2) NOT NULL,
 `ComponentVersion_component_id` SMALLINT(2) NOT NULL,
 `ComponentVersion_application_type` CHAR(1) NOT NULL,
 `ComponentVersion_application_year` YEAR NOT NULL,
 `ComponentVersion_application_department` VARCHAR(50) NOT NULL,
 `ComponentVersion_application_number` SMALLINT(5) UNSIGNED NOT NULL,
 PRIMARY KEY (`feedback_date`, `application_number`, `application_department`, `application_year`, `application_type`, `component_id`, `section_id`, `version`, `user_email`, `Component_version`, `Component_section_id`, `ComponentVersion_component_id`, `ComponentVersion_application_type`, `ComponentVersion_application_year`, `ComponentVersion_application_department`, `ComponentVersion_application_number`),
 CONSTRAINT `fk_LiaisonFeedback_idx`
   FOREIGN KEY (`feedback_date` , `application_number` , `application_department` , `application_year` , `application_type` , `component_id` , `section_id` , `version` , `user_email`)
   REFERENCES `rech_system`.`LiaisonFeedback` (`feedback_date` , `application_number` , `application_department` , `application_year` , `application_type` , `component_id` , `section_id` , `version` , `user_email`)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT `fk_ComponentVersion_LCFeedback`
   FOREIGN KEY (`Component_version` , `Component_section_id` , `ComponentVersion_component_id` , `ComponentVersion_application_type` , `ComponentVersion_application_year` , `ComponentVersion_application_department` , `ComponentVersion_application_number`)
   REFERENCES `rech_system`.`ComponentVersion` (`version` , `section_id` , `component_id` , `application_type` , `application_year` , `application_department` , `application_number`)
   ON DELETE CASCADE
   ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_LiaisonFeedback_idx` ON `rech_system`.`LiaisonComponentFeedback` (`feedback_date` ASC, `application_number` ASC, `application_department` ASC, `application_year` ASC, `application_type` ASC, `component_id` ASC, `section_id` ASC, `version` ASC, `user_email` ASC);

CREATE INDEX `fk_ComponentVersionFeedback_idx` ON `rech_system`.`LiaisonComponentFeedback` (`Component_version` ASC, `Component_section_id` ASC, `ComponentVersion_component_id` ASC, `ComponentVersion_application_type` ASC, `ComponentVersion_application_year` ASC, `ComponentVersion_application_department` ASC, `ComponentVersion_application_number` ASC);


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

此語句缺少索引列

21:53:48    CREATE INDEX `application_idx` ON `rech_system`.`Message` ()

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

相關問答

Mysql

MySQL - 刪除具有引用自身的外鍵約束的行

  • July 3, 2021
檢視問答
Mysql

約束 2 個外鍵具有相同的輔助列值

  • June 19, 2022
檢視問答
Mysql

如何維護訂單的客戶地址？

  • December 22, 2021
檢視問答
Mysql

可以創建每個外鍵唯一的約束嗎？

  • December 20, 2021
檢視問答
Mysql

兩個外鍵約束最方便的索引策略是什麼？

  • December 7, 2021
檢視問答
Mysql

線上學習：不同的使用者角色不同的領域

  • June 24, 2021
檢視問答