Last Updated: February 25, 2016
·
935
· gaqzi

MySQL adding foreign key, error 150

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.

When you get try to create a FOREIGN KEY but keep getting the error above in your InnoDB status, you see that your column definition is the same, and that you got an index in the table your trying to link from, so what else might it be? Have you looked at the charset for the tables/columns?

I just spent close to an hour before noticing that the legacy database had the original table in latin1 and the new table I was trying to link was in utf8.

So a quick ALTER TABLE tbl CONVERT TO CHARACTER SET utf8; and then the foreign key was in.