0

Im having trouble setting up foreign keys, it always resulted to (errno: 150 "Foreign key constraint is incorrectly formed"), even though the column data type and length are correct.

here is the table test

CREATE TABLE `test` (
  `test_column_one` VARCHAR(50) NOT NULL,
  `test_column_two` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`test_column_one`, `test_column_two`)
) ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

and this is the table which consist of the foreign keys

CREATE TABLE `test_two` (
  `test_no` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `test_one` VARCHAR(50) NOT NULL,
  `test_two` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`test_no`),
  INDEX `index_one_test_column_one` (`test_one` ASC),
  INDEX `index_two_test_column_two` (`test_two` ASC)
) ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

ALTER TABLE `test_two`
    ADD CONSTRAINT `fk_test_one`
    FOREIGN KEY (`test_one`)
    REFERENCES `test` (`test_column_two`);

ALTER TABLE `test_two`
    ADD CONSTRAINT `fk_test_two`
    FOREIGN KEY (`test_two`)
    REFERENCES `test` (`test_column_two`);

any idea where did I get wrong? Thank you in advance

Beginner
  • 1,700
  • 4
  • 22
  • 42

0 Answers0