Solutions to IT problems

Solutions I found when learning new IT stuff

MySQL varchar foreign key

with one comment


How to waste hours for a simple Issue

I wanted to add a varchar(8) foreign key to a table but MySQL consistently complained with errno 150. I really almost lost my mind as why this would not work. Also

SHOW ENGINE INNODB STATUS

did not reveal anything too meaningful:

FOREIGN KEY (`userId`) REFERENCES `user` (`userId`):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

 

The thing that caught my attention was

column types in the table and the referenced table do not match for constraint

 

I thought why would 2 varchar columns with same size not match in type? And of course the answer is obvious collation. Turns out the that in the new table the column was UTF-8 instead of ASCII as in the referenced table. Changed to ascii and done.

Never had such issues before but I’m more used to Oracle and AFAIK there is now “column level collation” in Oracle.

Advertisements

Written by kienerj

November 14, 2011 at 14:00

Posted in Database, MySQL

One Response

Subscribe to comments with RSS.

  1. Thanks for the post and solution. I don’t think I would have ever found that…

    boldSOCKS.com

    February 7, 2012 at 05:16


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: