MySQL foreign key creation error (MySQL error number 1005 errno: 150)

Http://yin123.blog.51cto.com/882581/533192 this is a very common error when create a MySQL table, even some people has take it as a MySQL bug and commit it to the official, I came up with this bothered problem these days also, after a crazy Google work, the source of the problem became clear, the following lines is a very good article that give that "bug" a conclusion, and I 'd like to translate it into simple Chinese in case you'll hit that problem again,

Concerning the personal talent, some mistakes can not be avoid, if you
Found any, please feel free to contact me, or leave a message below,
Thanks in advance.
Source URL:

Http://www.verysimple.com/blog/2006/10/22/mysql-error-number-1005-cant-create-table-mydbsql-328_45frm-errno-150/

Translation text:

This error often occurs when you try to create a foreign key in MySQL, which is very frustrating. A. frm cannot be created like this.
The file error seems to imply that the file permission of the operating system is incorrect or other reasons, but in fact these are not. In fact, the MySQL error has been reported as a MySQL error.
The bug has appeared in the MySQL developer list for many years, but it seems to be misleading.

In many instances, this error occurs because MySQL has never been able to support the problem, unfortunately, it does not specify which problem will lead to the above error. Below I will list the common causes of this terrible 150 error, and I sorted it by the possibility.

Known causes:

1. the type or size of two fields are not strictly matched. For example, if one field is int (10), the foreign key must be set to int (10) instead of INT (11) neither can it be
Tinyint. You must use the show command to check the field size, because some query browsers sometimes use int (10) and INT (11)
All are displayed as integer. In addition, you must determine whether one of the two fields is signed and the other is unsigned,
The two fields must be exactly matched. For more information about signed and unsigned
For information, see: http://www.verysimple.com/blog? P = 57

2. You have not created an index for one of the foreign keys you are trying to reference, or it is not a primary key. If one of them is not a primary key, you must create an index for it.

3. The foreign key name is an existing key value. In this case, you should check your database to ensure that the foreign key name is unique, or you can add a few random characters after the key name to test whether it is the cause.

4. One or two tables are MyISAM engine tables. To use foreign key constraints, they must be InnoDB Engine (in fact, if both tables are MyISAM engine tables, this error does not occur at all, but does not generate foreign keys). You can set the engine type of the table through the query browser.

5. You may have set the on Delete set null, but the related key field is set to the nots null value. You may modify the cascade attribute value or set the field attribute to allow null to fix this bug.

6. Make sure that your charset and collate options are consistent at the table level and field level.

7. You may have set a default value for the foreign key, for example, default = 0.

8. In this relationship, one of the fields is one of the hybrid key values, and it does not have its own independent index. In this case, you must create an independent index for it.

9. syntax errors in the alter Declaration

Black hair: http://heisetoufa.javaeye.com/

I encountered this problem today. I checked it online and made a record here.