-
Data Types Don't Match:
The types of the columns have to be the same (usually). This is one of the most common reasons for errno 150. For instance, if the type of the child column is VARCHAR(50), the type of the parent column should be exactly VARCHAR(50) (since they're supposed to hold the same data). For numeric types, if one is UNSIGNED, then both have to be UNSIGNED. They should match exactly!. I have run into circumstances where it has let me create a foreign key where the child column was a VARCHAR(50) and the parent column was a VARCHAR(200). Interestingly, if I tried to do the opposite for the same tables, reference a child column that was a VARCHAR(200) to a parent column that was a VARCHAR(50), it threw the errno 150 error. This all might depend on the version of MySQL you are using, and really, the data types should match exactly since the same data is being stored in both places.
How do you fix it? You need to check the data types for the columns. You can check them by using SHOW COLUMNS, or SHOW CREATE TABLE. If you are using Eliacom's MySQL GUI tool, then the system should alert you if their data types are different before it attempts to create the foreign key, so you shouldn't have to worry about this. If you don't know how to add foreign keys using Eliacom's MySQL GUI tool, see the video tutorial on adding foreign keys and indexes.
-
Parent Columns Not Indexed (Or Indexed in Wrong Order):
MySQL requires that both the child columns and parent columns have indexes on them so that the operations to ensure the constraint is in effect can be done quickly. If there isn't a key (index) on the child table, it will automatically create it. But if there isn't one on the parent table, then it will throw a (very unhelpful) error. Important: For multi-column foreign keys, you need a multi-column index. The order of the columns in the index matters! This means you could have an index on the two columns you're trying to match, but if they're in a different order than how you put them into the foreign key statement, you'll get this error.
How do you fix it? You need to check that you have an appropriate index on the parent table. If you are creating a foreign key on multiple columns, then you need to create an index on those columns in the right order. If you are creating a foreign key on one column, and that column has a multi-column index, then it should work if the column is the first in the index. I have heard that sometimes this doesn't work, but I've never been able to confirm that (let me know if you've had this experience). If you are using Eliacom's MySQL GUI tool, then the system will check if there is an appropriate index on the parent table. If there isn't, then it will automatically (and silently) create one for you. You can always view the indexes that exists on each table easily in the Table Manager as well. If you don't know how to add foreign keys (or view indexes) using our MySQL GUI tool
-
Column Collations Don't Match:
For character string type columns (CHAR, VARCHAR, etc.), the column collations have to match exactly. This of course means that the CHARACTER SETs have to match exactly as well. If they aren't, you can expect the errno 150 error.
How do you fix it? You need to check the collations for the columns to see if this might be the cause of the issue. The easiest way to do this using MySQL queries is using SHOW FULL COLUMNS. That will tell you the collation for each column in a table. If you are using Eliacom's MySQL GUI tool, then when you create the foreign key, our MySQL GUI tool will precheck the collations. If they are not the same, then it will tell you that they are different and need to be fixed before the foreign key can be implemented. If you don't know how to add foreign keys (or view indexes) using Eliacom's MySQL GUI tool
-
Using SET NULL on a NOT NULL Column:
If you try to execute a statement like:
ALTER TABLE `child_table` ADD FOREIGN KEY (`child_column`) REFERENCES `parent_table` (`parent_column`) ON DELETE SET NULL
and if the child_column's definition has NOT NULL in it, you will get the errno 150 error.
How do you fix it? This takes some thought. Do you really want to set the child to NULL if the parent is deleted (or updated if you did ON UPDATE SET NULL)? If so, you need to make sure that NULL is allowed for that column in the child table. You can check this using SHOW COLUMNS or SHOW CREATE TABLE. If you didn't really want that, then change the ON DELETE/UPDATE SET NULL to something like CASCADE or RESTRICT. If you are using Eliacom's MySQL GUI tool, then when you go to create the foreign key, the system will alert you if you are trying to SET NULL to a column that is NOT NULL, so you can decide if you want to change it. If you don't how know to add foreign keys using Eliacom's MySQL GUI tool/
-
Table Collations Don't Match:
Just like the Column Collations issue above, having different table collations, even though the column collations match, can cause some problems (at least on some versions of MySQL; this may have been fixed in later versions since this in principle the table default shouldn't matter). Where we have seen this error crop up is if you have two tables with different collations, but the column collations are the same: it did allow us to create the foreign key without any errors (this was done on MySQL 5.1.41). However, if we ever attempted to modify the child column at all (say rename it, or even just run a "MODIFY COLUMN" query that kept its attributes the same, we would get the errno 150 error. In this case, SHOW INNODB STATUS was completely unhelpful. It said something about needing indexes, or that we possibly SET NULL on a NOT NULL column.
Note: Actually in the case we found, it was different default character sets at the table level, but I'm guessing it happens if only the collations are different as well.
How do you fix it? You'll have to change the table collations to match as well as the column collations. It's possible that in more recent versions of MySQL that this has been fixed. If you are using Eliacom's MySQL GUI tool, then you can change the table collation by using our MySQL GUI's table editor. If you don't know how to edit tables using our MySQL GUI tool.
-
Parent Column Doesn't Actually Exist In Parent Table:
This is the kind of error that you will spend hours looking for, and then kick yourself when you find it. Check your spelling and look for spaces! Take for instance the query below:
alter table esp_empdata add constraint foreign key (`empClass`) references `esp_empclasses` (` id2`)
I went through all the other checks in this paper over and over about 50 times. I was about to give up hope, when I discovered the trick of using SHOW INNODB STATUS, and it will actually take all the mystery away of why these foreign key errors are happening. It told me "Cannot resolve column name close to: ". I thought, what does that mean? I looked more closely, realized I had a space before id2, and kicked myself, repeatedly. You will get the errno 150 error.
How do you fix it? Double check that the column that you are trying to reference actually exists. If it checks out, then triple check for things like spaces at the beginning or end of the column, or anything that might make it miss the column in the parent table.
-
One of the indexes on one of the columns is incomplete (column is too long)
Even if you have added an index to a column, if it's not complete, it will fail. This can happen if your column takes up more than 767 bytes. Note that for a UTF8 column, that corresponds to a varchar(255); a column longer than that cannot have a foreign key on it (note that on some versions you can increase this max key length).
How do you fix it?Make sure teh key is complete and/or make the column shorter.
Saturday, 20 February 2016
MySQL errno 150
Thursday, 4 February 2016
Subscribe to:
Posts (Atom)