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

Question

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

Rahul · Accepted Answer · 2016-01-29 19:22:02Z

1

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`)

edited Jan 29, 2016 at 19:22

answered Jan 29, 2016 at 19:10

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

N.Mehta Over a year ago

but i need to create FK in date table that reffer to date_id in this log table.and one for list_activities that i mention above, Date table is created. thanks

N.Mehta Over a year ago

but it will reffer to date_id coloumn of log table . activity_type_id is diffrent that is reffer by another table.

BK435 Over a year ago

Since PK is pair, you have to create a foreign key that refers to two fields as well. You may want to consider your schema and how you are normalizing these tables.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related