Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the reasons for the failure of establishing foreign keys in the MySQL database, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

What are the reasons for the failure of establishing foreign keys in MySQL database

1. The types or sizes of the two fields do not strictly match. 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 check the size of the field, because some query browsers sometimes display both int (10) and int (11) as integer. In addition, you must also determine whether one of the two fields is SIGNED and the other is UNSIGNED. These two fields must match exactly the same. For more information about signed and unsigned, see: http://www.verysimple.com/blog/p=57.

two。 One of the foreign keys you are trying to reference is not indexed, or it is not a primarykey. If one of them is not primarykey, you must create an index for it.

3. The name of a 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, or you should add a few random characters after the key name to test if this is the reason.

4. One or two of the tables are MyISAM engine tables, and if you want to use foreign key constraints, you must be InnoDB engine, (in fact, if both tables are MyISAM engine, this error will not occur at all, but will not generate foreign keys), you can query the browser to set the engine type of the table.

What are the reasons for the failure of establishing foreign keys in MySQL database

5. You may have set ONDELETESETNULL, but the field of the related key is set to the NOTSNUL value. You can fix this bug by changing the value of the cascade property or setting the field property to allownull.

6. Please 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 foreign keys, 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, so you must create a separate index for it.

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

After reading the above, do you have any further understanding of the reasons for the failure to set up foreign keys in the MySQL database? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.