Parse MySQL to create foreign key association error errno:150

This error often happens when you try to create a foreign key in mysql, which is very frustrating. Cannot create like this one. frm file error seems to suggest that the operating system file permissions error or other reasons, but in fact, these are not, in fact, the mysql error has been reported is a mysql bug itself and appear in mysql developers list for many years, however, it seems that this is one kind of misleading.

In many instances, the happening of this kind of mistake because mysql1 straight since cannot very good support, the problem of the relationship between more unfortunately it didn't specify what a problem can cause the above error, below I put in a common cause of this terrible 150 error list, and I'm the size of the possibility to made the order

Known causes:

For example, if one is INT(10), the foreign key must also be set to INT(10), not INT(11) and not TINYINT. You have to use the SHOW command to see the size of the field, because some query browsers sometimes display int(10) and int(11) as integer. In addition, you must also determine whether two fields is one for SIGNED, while the other one is UNSIGNED, these two fields must strictly 1 to match, more information about signed and unsigned, please refer to: http: / / www verysimple. com blog /? p = 57

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

3, the name of the foreign key is an existing key value. At this point, you should check your database to make sure that the foreign key name is unique to 1, or you can add a few random characters after the key name to test if this is the case.

If you want to use the foreign key constraint, you must use the InnoDB engine. (in fact, if both tables belong to the MyISAM engine, this error will not happen at all, but no foreign key will be generated either.) you can set the engine type of the table by querying the browser

5. You may have set ON DELETE SET NULL, but the relevant key field is set to NOTS NULL. You can fix bug by changing the property value of cascade or by setting the field property to allow null.

6. Make sure your Charset and Collate options are 1 to 1 at the table and field levels

7, you may have set a default value for the foreign key, such as default=0

8, in this relationship, one of the fields is one of the mixed key values, it does not have its own independent index, at this point, you must create a separate index for it.

9, there is a syntax error in the ALTER declaration

10. The two tables to join are encoded in different formats