Summary of reasons for the failure of MySQL database to establish foreign keys

http://database.51cto.com/art/201108/280088.htm when creating foreign keys in the MySQL database , there are often some errors that can be frustrating. A typical error is: Can ' t create Table ... The error. In many instances, this kind of error occurs because MySQL has not been able to support the problem of the relationship, and, unfortunately, it does not indicate which problem will lead to the above error, and below I put the cause of this terrible 150 common causes listed, And I sort by the size of the possibility, the known reason:

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

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

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

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

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

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

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, and it does not have its own independent index, at which point you must create a separate index for it.

There is a syntax error in the 9.ALTER declaration.

The above is a summary of the possible reasons for MySQL to create foreign key failure, if you want to learn more about the MySQL database content, you can look at the article here: http://database.51cto.com/mysql/, I believe it will bring you the harvest!

Summary of reasons for the failure of MySQL database to establish foreign keys