In a typical MySQL installation, the default character set and collation are latin1 and latin1_swedish_ci. This means that all string comparisons will be performed in a case insensitive manner. So, ‘Search String’='search string’. This is great for most WHERE expressions, however there are occasions where performing a case-sensitive search is required. Luckily MySQL provides the collate clause which allows you to override whatever the default collation is for a comparison. As an example :
select * from mytable where first_name collate latin1_general_cs=’damon’
This will only return rows will the first_name matches damon exactly. Damon will not match.
You can also use latin1_bin which performs an exact binary match.
If you are unsure of a field or strings collation, you can use the COLLATION(str) function to return the collation of the string argument.
If you have seen this error, don’t worry, you are not alone. This error is often accompanied by a message that a table or file could not be created, and usually happens when creating a foreign key. In my experience, 99% of the time this is due to an incompatibility between the two fields in the foreign key. Usually it is something simple like unsigned integer to signed integer. The trickiest I have seen is when trying to create a foreign key between two CHAR fields and they do not share the same CHARSET and COLLATE. ALTER the table so that the CHARSET and COLLATE are the same, and try to add the foreign key again.
Let me know if you have other examples of how you have worked around errno: 150 when adding foreign keys.
Filed in Errors, MySQL
|
Also tagged ALTER, CHARSET, Foreign, foreign key, incompatibility, Innodb, integer, key, MySQL, table, UNSIGNED, unsigned integer
|