Mysql to establish a foreign key failed several situations record can ' t create table _mysql

An error like this cannot create a. frm file seems to imply incorrect permissions or other reasons for operating system files, but in reality, this MySQL error has been reported as a bug in MySQL itself and has been in the MySQL developer list for many years, Yet this seems another misleading.

In many instances, this error occurs because MySQL has always been unable to support the relationship of the problem, and, unfortunately, it does not indicate exactly which one of the problems will lead to the above kind of error, below I put the cause of this terrible 150 common causes of the error listed, And I have sorted out the size of probabilities, known reasons:

1, two fields of type or size do not exactly match, for example, if one is int (10), then the foreign key must also be set to int (10), not int (11) nor can it be 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 integers. In addition, you must also determine whether two fields are one signed and the other is unsigned, which must be closely matched, and for more information about signed and unsigned, see: http://www.verysimple.com/blog/?p=57

2, you are trying to reference one of the foreign keys did not establish an index, or not a primary key, if one of them is not primary key, you must create an index for it.

3, the name of the foreign key is an already existing a key value, this time, you should check your database to ensure that the name is unique, or you add a few random characters after the key name to test whether this is the reason.

4, one or two tables are MyISAM engine tables, if you want to use a FOREIGN key constraint, you must be a InnoDB engine, (in fact, if two tables are MyISAM engines, this error does not occur at all, but it does not produce foreign keys), you can set the engine type of the table by querying the browser

5, you may set the on DELETE set NULL, but the related key field is set to the NOTS null value. You can fix the bug by modifying the Cascade property value or by setting the field property to allow null.

6, please make sure that your charset and Collate options are consistent at the table level and field level

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

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

9. There are grammatical errors in ALTER statement

Finally: the establishment of foreign keys a bit troublesome, you can directly generate DDL with MyEclipse ...