MySQL Error Code 1215: Cannot add foreign key Constraint on ALTER TABLE : Mysql

Question

I want to add foreign key Constraint on my existing tables(type InnoDB) ,I have two tables listed below:

tbl_users : [ user_id(INT,AUTO_INCREMENT), user_name,--- etc]
tbl_users_meta : [ user_meta_id(INT,AUTO_INCREMENT), user_id(INT),--- etc]

I have already created index 'user_id' on 'tbl_users_meta' listed below :

Here is my Query but i am getting (MySQL Error Code 1215: ) each time.what is the problem i can getting it ?

ALTER TABLE `tbl_users_meta` 
ADD  CONSTRAINT `fk users user_id`
FOREIGN KEY (`user_id`) REFERENCES 
`sanskrut`.`tbl_users`(`user_id`)
ON DELETE NO ACTION ON UPDATE CASCADE;

possible duplicate of MySQL error cannot add foreign key constraint — Matt Gibson
– Matt Gibson, Commented Aug 20, 2015 at 13:52
i am working at localhost WAMP server, where i can find MySQL error log ? — amit gupta
– amit gupta, Commented Aug 20, 2015 at 14:03

Ritobroto Mukherjee · Accepted Answer · 2015-08-20 12:25:06Z

0

TRY with the same query you have writen with a small modification:

ALTER IGNORE TABLE `tbl_users_meta` 
ADD  CONSTRAINT `fk users user_id`
FOREIGN KEY (`user_id`) REFERENCES 
`sanskrut`.`tbl_users`(`user_id`)
ON DELETE NO ACTION ON UPDATE CASCADE;

Hope it'll work

answered Aug 20, 2015 at 12:25

Ritobroto Mukherjee

1111 silver badge13 bronze badges

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

2 Comments

amit gupta Over a year ago

getting same error using this - #1215 - Cannot add foreign key constraint

Ritobroto Mukherjee Over a year ago

have you tried specifying database_name preceding both the table names?

Matt Gibson · Accepted Answer · 2015-08-23 10:56:33Z

I can't see anything wrong with what you've written, so there must be something you haven't posted affecting the problem. For example, on MySQL version 5.5.44, I can successfully execute the following:

CREATE DATABASE sanskrut;
USE sanskrut;
CREATE TABLE tbl_users (user_id INT AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(50));
CREATE TABLE tbl_users_meta (user_meta_id INT AUTO_INCREMENT PRIMARY KEY, user_id INT);
CREATE INDEX user_id ON tbl_users_meta (user_id);
ALTER TABLE `tbl_users_meta` 
ADD  CONSTRAINT `fk users user_id`
FOREIGN KEY (`user_id`) REFERENCES 
`sanskrut`.`tbl_users`(`user_id`)
ON DELETE NO ACTION ON UPDATE CASCADE;

...which finishes with your exact problem statement, copied and pasted, and works just fine.

I'd suspect, as I mentioned in the comments, that there's an index missing or possibly a datatype mismatch. You may find it helpful to add the output of:

DESC tbl_users;
DESC tbl_users_meta;
SHOW indexes FROM tbl_users;
SHOW indexes FROM tbl_users_meta;

...to your question.

Collectives™ on Stack Overflow

MySQL Error Code 1215: Cannot add foreign key Constraint on ALTER TABLE : Mysql

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related