Why can not I give a foreign key constraint?

Question

ERROR 1215: Cannot add foreign key constraint

SQL Statement:

CREATE TABLE `arena`.`like_history` (

  `p_id` INT(11) NOT NULL,

  `u_id` INT(11) NOT NULL,

  `ldate` DATETIME NOT NULL,

  PRIMARY KEY (`p_id`, `u_id`),

  INDEX `u_id_idx` (`u_id` ASC),

  CONSTRAINT `p_id`

    FOREIGN KEY (`p_id`)

    REFERENCES `arena`.`picture` (`p_id`)

    ON DELETE CASCADE

    ON UPDATE CASCADE,

  CONSTRAINT `u_id`

    FOREIGN KEY (`u_id`)

    REFERENCES `arena`.`user` (`ul_id`)

    ON DELETE CASCADE

    ON UPDATE CASCADE)

possible duplicate of [MySQL Error 1215: Cannot add foreign key constraint](http://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint) — , May 01 '14 at 08:13
Do the tables `picture` and `user` definitely exist in `arena`? Are their primary keys the `p_id` and `ul_id` respectively (not `u_id` for the second one???) — Damien_The_Unbeliever, May 01 '14 at 08:13
1 - show the table structure of `arena`.`picture` and `arena`.`user`; 2 - what are database engines for all these 3 tables? — Andy W, May 01 '14 at 08:14

score 0 · Answer 1 · answered May 01 '14 at 08:27

I am not sure how the referenced table columns are defined.
But for them to be eligible for referring from other tables, they MUST have INDEXed

As per documentation on foreign key constraints:

REFERENCES tbl_name (index_col_name,...)

Define an INDEX on both picture.p_id and user.ul_id columns.

And other primary constraints are that,

The child column definition MUST match with that of parent column.
Database ENGINE type must be same.

Refer to:

MySQL Using FOREIGN KEY Constraints

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION

Why can not I give a foreign key constraint?

1 Answers1