MYSQL Workbench - ERROR: Error 1215: Cannot add foreign key constraint

Question

I am trying to forward engineer a database on MySQL Workbench, but I continually am getting this error message. I feel as though it is most likely an issue with the structure of my relationships, but I cannot find a discrepancy.

Here is an image of the schema diagram.

Here is the error message I am receiving:

    Executing SQL script in server
ERROR: Error 1215: Cannot add foreign key constraint
SQL Code:
        -- -----------------------------------------------------
        -- Table `bturpin`.`THREAD`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `bturpin`.`THREAD` (
          `id` INT NOT NULL AUTO_INCREMENT,
          `answer` DOUBLE NULL,
          `comment` VARCHAR(255) NULL,
          `test_subcategory_name` VARCHAR(45) NULL,
          `RATING_id` INT NOT NULL,
          `RATING_INSTRUCTOR_id` VARCHAR(30) NOT NULL,
          `RATING_INSTRUCTOR_LESSON_title` VARCHAR(45) NOT NULL,
          `RATING_INSTRUCTOR_LESSON_COURSE_num` VARCHAR(15) NOT NULL,
          `RATING_RATER_id` VARCHAR(30) NOT NULL,
          PRIMARY KEY (`id`, `RATING_id`, `RATING_INSTRUCTOR_id`, `RATING_INSTRUCTOR_LESSON_title`, `RATING_INSTRUCTOR_LESSON_COURSE_num`, `RATING_RATER_id`),
          INDEX `fk_THREAD_RATING1_idx` (`RATING_id` ASC, `RATING_INSTRUCTOR_id` ASC, `RATING_INSTRUCTOR_LESSON_title` ASC, `RATING_INSTRUCTOR_LESSON_COURSE_num` ASC, `RATING_RATER_id` ASC),
          CONSTRAINT `fk_THREAD_RATING1`
            FOREIGN KEY (`RATING_id` , `RATING_INSTRUCTOR_id` , `RATING_INSTRUCTOR_LESSON_title` , `RATING_INSTRUCTOR_LESSON_COURSE_num` , `RATING_RATER_id`)
            REFERENCES `bturpin`.`RATING` (`id` , `INSTRUCTOR_id` , `INSTRUCTOR_LESSON_title` , `INSTRUCTOR_LESSON_COURSE_num` , `RATER_id`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB

SQL script execution finished: statements: 14 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

Possible duplicate of [MySQL Error 1215: Cannot add foreign key constraint](http://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint) — Jocelyn, Jul 01 '16 at 21:21
Make sure that `RATING` has a composite index on `(id , INSTRUCTOR_id , INSTRUCTOR_LESSON_title , INSTRUCTOR_LESSON_COURSE_num , RATER_id)` — Barmar, Jul 01 '16 at 21:57
You can only have a foreign key referencing a unique field. And I would also suggest if you can try to make the data type of and length of the foreign key and referring key same? — Dharam, Jul 01 '16 at 22:18

score 0 · Answer 1 · edited May 23 '17 at 12:01

Try forwarding in this order :

Manually create the fields which must have a foreign key constraint in MySQL WB
Forward engineer to your DB
Manually create the foreign key constraints in MySQL WB
Forward engineer angain

You may also consider using only integer foreign key.

This seems to be a MySQL Workbench issue with foreign key on non integer key.

Source : Error 1215: Cannot add foreign key constraint

MYSQL Workbench - ERROR: Error 1215: Cannot add foreign key constraint

1 Answers1

Linked