0

Problem:

I am receiving the

Error 1215: Cannot add foreign key constraint

error while running my MySQL schema code generated from the EER Diagram I used to model my database.

The Error occurs when adding table Person (and I suspect table Message may give the same issue too)

Searching for solutions:

Overall, the most likely problem which appears is a lack of an index from the parent (identifying) entity where multiple columns are indexed.

see 1) 2) - Possible causes of 1215 error

Searching on DBA Stackexchange, simple human errors caused issues, none which appear to relate to my issue: - post - another post

See full database SQL below: (You can import this into MySQL Workbench to see Entity models)

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

I have no idea what is causing the problem, why the foreign key constraint is problematic.

Advice would be appreciated!

Improve this question
0

1 Answer 1

Reset to default
1

I was unable to import ER diagram.It seems foreign key constraints have same names in multiple tables,just renamed conflicting constraint names.

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

Index column missing for this statement

21:53:48    CREATE INDEX `application_idx` ON `rech_system`.`Message` ()
Improve this answer
2
  • Thank you very much for your effort! Could you please elaborate on how you were able to 'debug' this SQL code, specifically the renaming constraints (how did you know it is an issue - I thought this was not a problem due to the constraint name is limited in scope by the table) and the missing index column (why is this necessary)?
    – CybeX
    Commented Jun 25, 2018 at 4:50
  • 3
    Constraint names should be unique in each schema(error was "cannot write duplicate key..." that narrowed to primary or foreign keys). Started creating individual table one by one starting from the table throwing error (AgendaItem) eliminating errors one by one. There is syntax error in Create index ,mysql doesn't know where to create index on table Message i.e column name missing.
    – user153556
    Commented Jun 25, 2018 at 5:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.