CODEWITHSUNDEEP
mysql
N.Mehta
N.Mehta

Reputation: 11

mysql - Not able to create table error in foreign key constraint

CREATE TABLE IF NOT EXISTS `nm`.`list_activities` (
  `activity_id` INT NOT NULL COMMENT '',
  `activity_name` VARCHAR(45) NULL COMMENT '',
  `activity_type_id` INT NOT NULL COMMENT '',
  PRIMARY KEY (`activity_id`, `activity_type_id`)  COMMENT '',
  CONSTRAINT `fk_list_activities_log`
    FOREIGN KEY (`activity_id`)
    REFERENCES `nm`.`log` (`activity_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `nm`.`log` (
  `date_id` INT NOT NULL COMMENT '',
  `activity_id` INT NOT NULL COMMENT '',
  PRIMARY KEY (`date_id`, `activity_id`)  COMMENT '')
ENGINE = InnoDB;

Not able to create list_activities table,It display the error message

Error Code: 1215. Cannot add foreign key constraint

in mysql

Upvotes: 1

Views: 54

Answers (1)

Rahul
Rahul

Reputation: 77936

That's because you have composite primary key and you are creating FK only on key one column and so nothing but creating a partial functional dependency.

PRIMARY KEY (`date_id`, `activity_id`)

   FOREIGN KEY (`activity_id`)
    REFERENCES `nm`.`log` (`activity_id`)

You will have to create a FK referencing both the key column to solve this issue. Change your FK definition to be

   FOREIGN KEY (`activity_id`, `activity_type_id`)
    REFERENCES `nm`.`log` (`activity_id`, `date_id`)

Upvotes: 1

Related Questions