MySQL Cannot add Foreign Key Constraint to tables

Question

I am having trouble adding Foreign Key constraints to my database.

I am running the InnoDB engine on all tables, and have insured the data type and size are the same in both tables.

Also, I have tried using the MySQL command line, phpMyAdmin and MySQL workbench, but no luck.

Since in MySQL workbench does not allow me to check the Index Columns, nor select the Referenced Column, there is another issue I am overlooking.

Is there something else I should be looking for?

I do have test data in almost all of the tables, would that matter?

EDIT: When I do see an error it is usually "#1005 - Can't create table 'db_name.#sql-43d_8e' (errno: 150)" , that talks about data mismatching, according to the documentation...Otherwise MySQL Workbench won't let me do it, so I do no know the problem.

What exactly do you mean with "having trouble"? An error message? If yes, which? Are FKs not enforced? Do you have the error when adding the FK or when insert/updating rows? — user330315
– user330315, Commented Jan 16, 2012 at 18:16
Yea, I think you were saying what boobiq posted below...I wasn't thinking about missing data. I'm going to try to remove all the data from the tables and try again. Thanks! — guyfromfl
– guyfromfl, Commented Jan 16, 2012 at 18:16

hcker2000 · Accepted Answer · 2012-08-28 13:09:14Z

4

You should also check to make sure your columns match. The issue I was having is that I did not notice one was a signed int and the other unsigned.

answered Aug 28, 2012 at 13:09

hcker2000

6151 gold badge6 silver badges31 bronze badges

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

Comments

boobiq · Accepted Answer · 2012-01-16 18:13:09Z

1

That would matter, if you have data, that can't be linked correctly with foreign keys, for example you have entry in "comments" with id_article = 10, but you don't have entry in "articles" with id = 10 .. in that case, you can't add foreign key

answered Jan 16, 2012 at 18:13

boobiq

3,0242 gold badges22 silver badges27 bronze badges

1 Comment

guyfromfl Over a year ago

hmm let me clear the tables and try again... Since I added data with out the constraints to keep the data integrity, this might very well be the problem.

Boris · Accepted Answer · 2013-09-08 07:06:23Z

1

I had the same problem, datatype of the primary key in the referenced table and the foreign key were the same yet i couldn't create the second table because mysql couldn't add the foreign key constraint. for some reason it was the sequence in which i created the tables. the error occurred if i created the first table, added the data then created the second table, if i created the first table and second tables before i added the data to the first table there was no error message.

answered Sep 8, 2013 at 7:06

Boris

111 bronze badge

Collectives™ on Stack Overflow

MySQL Cannot add Foreign Key Constraint to tables

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related