0

I am having a problem creating a foreign key on my child table.

This is the parent table:

CREATE TABLE plan_det
(
  plan_id     INT NOT NULL,
  position    INT NOT NULL,
  exercise_id INT NOT NULL,
  sets        INT NULL,
  reps        INT NULL,
  PRIMARY KEY (plan_id, position),
  CONSTRAINT fk_workout_det_workout_def1
  FOREIGN KEY (plan_id) REFERENCES plan_def (id),
  CONSTRAINT fk_plan_det_exercise1
  FOREIGN KEY (exercise_id) REFERENCES exercise (id)
);
CREATE INDEX fk_plan_det_exercise1_idx
  ON plan_det (exercise_id);

This is the child table:

CREATE TABLE workout_exercise
(
  workout_id     INT      NOT NULL,
  datetime_start DATETIME NOT NULL,
  plan_position  INT      NOT NULL,
  PRIMARY KEY (workout_id, datetime_start, plan_position),
  CONSTRAINT fk_workout_exercise_workout_workout_id
  FOREIGN KEY (workout_id) REFERENCES workout (workout_id)
);

Until this point, all worked fine.

But when I try to create a foreign key from workout_exercise.plan_position to plan_det.position like so:

ALTER TABLE workout_exercise
            ADD CONSTRAINT workout_exercise_plan_det_position_fk
            FOREIGN KEY (plan_position) REFERENCES plan_det (position)

I get the error:

[HY000][1005] Can't create table 'web39_db8.#sql-5079_138f8f9' (errno: 150)

I can't explain this error, because both columns have the exact same type...

Palm
  • 699
  • 1
  • 6
  • 17
  • Is [this question](https://stackoverflow.com/questions/16227199/mysql-errno-150) related? – tadman Sep 03 '17 at 17:31
  • I close this as a duplicate of https://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150 which is a protected question, and has the best checklist of requirements for foreign keys. – Bill Karwin Sep 03 '17 at 17:50
  • Hint: in your case, you are referencing `plan_det.position` but that column is not the left-most column in the primary key. – Bill Karwin Sep 03 '17 at 17:53

0 Answers0