Error on rename of errno 150

MySQL errno 150

ERROR 1005 (HY000): Can’t create table ‘table’ (errno: 150)

ERROR 1025 (HY000): Error on rename of ‘table’ to ‘newtable’ (errno: 150)

Causes and Solutions for errno 150

1. Data Types Don’t Match: Click for solution

   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.

2. Parent Columns Not Indexed (Or Indexed in Wrong Order): Click for solution

   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, see the video tutorial on adding foreign keys and indexes.

3. Column Collations Don’t Match:Click for solution

   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, see the video tutorial on adding foreign keys and indexes.

4. Using SET NULL on a NOT NULL Column: Click for solution

   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, see the video tutorial on adding foreign keys and indexes.

5. Table Collations Don’t Match: Click for solution

   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, see the video tutorial on editing tables.

6. Parent Column Doesn’t Actually Exist In Parent Table: Click for solution

   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.

7. One of the indexes on one of the columns is incomplete (column is too long) Click for solution

MySQL errno 121

ERROR 1005 (HY000): Can’t create table ‘table’ (errno: 121)

ERROR 1025 (HY000): Error on rename of ‘table’ to ‘newtable’ (errno: 121)

Causes and Solutions for errno 121

1. Constraint Name Taken: Click for solution

   The constraint name that you picked is already taken. If you’re wondering what the constraint name is, in the example below where you are altering a table adding a foreign key, the constraint name is in blue:

   ALTER TABLE `child_table` ADD CONSTRAINT `some_constraint_name` FOREIGN KEY `key_name` (`child_column`)
   REFERENCES `parent_table` (`parent_column`)

   If the constraint name happens to be taken, you will get an errno 121.

   How do you fix it? If you’re explicitly choosing a constraint name, then choose something different (since what you chose is apparently already taken). Or you can let MySQL automatically set it for you by not choosing one at all (that’s what I recommend). If you really want to set your own, you can check what the other names are by looking in `information_schema`.`table_constraints` in your MySQL server, to see what’s taken. If you’re relatively certain which table has the constraint that has taken your name, then you can use SHOW CREATE TABLE to view them. If you are using Eliacom’s MySQL GUI tool, then when you go to create the foreign key, the system will precheck your name, and alert you if the name you chose is already taken. If you don’t how know to add foreign keys using Eliacom’s MySQL GUI tool, see the video tutorial on adding foreign keys and indexes.

2. Two Tables With Same Name But Different Case: Click for solution

   One of the most difficult times I ever had tracking down a foreign key error. What was weird was is it worked on one server, but not on another, which I thought were identical installations. The one difference between the two was that one server had case sensitive table naming turned on and the other didn’t. On the server with the case sensitive table naming turned on, I attempted to run the following query:

   alter table esp_empData add constraint foreign key (`empClass`) references `esp_empclasses` (`id2`)

   When I ran the above query, I got the following error:

   ERROR 1025 (HY000): Error on rename of ‘./otb10/#sql-37c_2f534’ to ‘./otb10/esp_empData’ (errno: 121)

   This was not good. It turned out I had another table with the same name but all lowercase(esp_empdata).

   How do you fix it? When this is the case (you have two tables that are the same name, but with case differences), you’re stuck. There’s nothing you can do (or at least as far as I could see) but to change the table name of the upper case table to something different (adding a «2» to the end fixed it). Luckily, it was was a mistake I had these two tables. And honestly, you really shouldn’t have more than one table named the exact same thing other than their case being different. So just don’t do that.

Other Foreign Key Errors You Might Encounter

1. A Foreign Key Constraint Fails: Data Doesn’t Match: Click for solution

   The most common but easy error to track (because it actually tells you what is wrong, is when you have data in the child table that does not match to the parent table. If you get this error, chances are you don’t have any of the problems below, since it actually got to the point of checking the data. However, if you’re getting this error and you’re SURE that you don’t have any bad data see «Duplicate Foreign Keys» below. Your error probably looks something like this when you go to create the foreign key (it will vary based on your columns):

   ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`database`.`#sql-37c_2f534`, CONSTRAINT `#sql-37c_2f534_ibfk_5` FOREIGN KEY (`child_column`) REFERENCES `parent_table` (`parent_column`))

   How do you fix it? To fix this, you need to find all the child values and get rid of them, either by setting them to NULL (if that’s allowed), or by making them actually allowed values. If you’re pretty MySQL savvy, you can make a query to check what values are offending. It should look something like:SELECT `child_table`.`child_column` from `child_table` where `child_table`.`child_column` IS NOT NULL AND NOT EXISTS (SELECT * FROM `parent_table` WHERE `parent_table`.`parent_column`=`child_table`.`child_column`)
   It will look a little different if you have a multiple column foreign key. If any rows are returned, those are the offenders. If you are using Eliacom’s MySQL GUI tool to create the foreign key, if there are any offending child values, it will list them when you attempt to create the foreign key, to help you find the values that need to be fixed. 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.

   How not to fix it: Some people say that you can use the query «SET foreign_key_checks=0» to get around this. That is true, but don’t do this, unless you think that the discrepancy is temporary. For instance, this is useful when cloning a database. If you copy over a child table data before the parent table data, the parent values won’t be there to start with, so the foreign key constraint will fail. In this case you should use foreign_key_checks=0, because once the parent table is in place everything will match. But if this isn’t used carefully, you can end up with child data that doesn’t match to any parent data in your child table. Use wisely!

2. No Error, but Foreign Key Won’t Create: Table Isn’t InnoDB: Click for solution

   Both tables need to be using the InnoDB Engine. If they don’t, then MySQL will NOT throw an error, but it also won’t create the foreign key. It just silently dies. This is probably worse than actually getting an error. So if you create a foreign key, and then the foreign key isn’t there, see if you are using the InnoDB Engine for both the child and parent tables. If you do this, you will get:

   No error at all!

   How do you fix it? You will have to change the engine for your tables. You can do this by doing ALTER TABLE `tableName` ENGINE=InnoDB; If you’re using Eliacom’s MySQL GUI tool, then when you go to create the foreign key, it will check the engines and throw an error if they don’t match before attempting to do the query. If you don’t how know to add foreign keys using Eliacom’s MySQL GUI tool, see the video tutorial on adding foreign keys and indexes.

3. Identifier Name is Too Long: Click for solution

   This is an issue with the fact that the MySQL doesn’t allow any identifier names to be longer than 64 characters. Note, that if your table name is pushing 64 characters, then the way that MySQL creates the default constraint name is using the table, and a suffix/prefix appended to it so that it might exceed the number of characters. Apparently, this error sometimes allows things like table creation even though the foreign key creation failed. If you have this problem, you will get an error that looks like this:

   ERROR 1059 (42000): Identifier name ‘myreallyreallyreallyreallyreallllllllllyreallyreallyreallyreallyreallylongname’ is too long

   How do you fix it?This one is more tricky. If it’s because you have a really long table name, then you can’t let MySQL assign the foreign key name automatically since it will throw the error. So you will have to set it manually. See the syntax in the Foreign Key White Paper for how to set this manually. If you are using Eliacom’s MySQL GUI tool, then when you go to create the foreign key, there is a spot in the foreign key creation form for you to create your own foreign key constraint name. If you don’t how know to add foreign keys using Eliacom’s MySQL GUI tool, see the video tutorial on adding foreign keys and indexes.

4. A Foreign Key Constraint Fails (Duplicate Foreign Keys): Click for solution

   I have gotten the «foreign key constraint fails» error a few times, when I’ve tried to update a parent table (with an ON UPDATE CASCADE foreign key, but it might happen with other types). I have double-double checked there is no data that is bad. In the end, the problem was that I had two identical foreign keys. It appears that when one tried to update the child table, the other caught it as an attempt to change the child table. The indicator that this is your problem is if you are updating the parent table, and it complains about the parent/child relationship. You should get an error that looks like this:

   ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`database`.`#sql-37c_2f534`, CONSTRAINT `#sql-37c_2f534_ibfk_5` FOREIGN KEY (`child_column`) REFERENCES `parent_table` (`parent_column`) ON UPDATE CASCADE)

   How do you fix it? Easy one. Just delete the duplicate foreign key. To get the foreign key names, you can use SHOW CREATE TABLE to see what the constraint names are to delete them. If you are using Eliacom’s MySQL GUI tool, you can delete the foreign key from the «Foreign Keys» tab for that table. If you don’t how know to find foreign keys using Eliacom’s MySQL GUI tool, see the video tutorial on foreign keys and indexes.

5. Anything else?: Click for solution

   If you’ve run into something that doesn’t seem to be here, let us know. Even if you figured it out, we’d love to help out future generations.

Frustrated?

There are many reasons why you can get foreign key errors, and often very different reasons give the same error, which is why it’s sometimes so hard to track down exactly what is the cause. The purpose of this white paper is to create an exhaustive list of the reasons why you get these error, and then to expand on that list with other things that can go wrong when trying to create foreign keys. Again, if you happen to run into a situation we don’t cover, please let us know so we can try to help you, and so we can put the information here to help future generations?

We hope you found this white paper useful. Please let us know if you have any questions you felt were not addressed in the white paper or if you have any feedback: Contact Us