What to do if mysql fails to add foreign key

Mysql solution to failure to add foreign keys: 1. Check whether the types or sizes of the two fields match and modify them; 2. Create an index for the field where the foreign key is set; 3. Check the engine type of the table, And modify it to InnoDB engine; 4. Check whether the foreign key name is unique and modify it; 5. Modify the attribute value of cascade or set the field attribute to allow null, etc.

The operating environment of this tutorial: Windows 10 system, MySQL version 5.7, Dell G3 computer.

What should I do if mysql fails to add a foreign key?

MySQL foreign key creation failure reasons

First of all, if data has been inserted into several tables related to the foreign key, it may cause the foreign key insertion to fail

When creating foreign keys in MySQL, we often encounter problems and fail. This is because there are many details in MySQL that we need to pay attention to. I have summarized and reviewed the information and listed the following common reasons. .

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), and not tinyint. In addition, you must also determine whether one of the two fields is signed and the other is unsigned (ie: unsigned). The two fields must match strictly. For more information about signed and unsigned, please see: http: //www.verysimple.com/blog/?p=57

2. The field trying to set a foreign key is not indexed, or is not a primary key. If one of them is not the primary key, you must first create an index for it.

3. One or two of the tables are MyISAM engine tables. If you want to use foreign key constraints, the table must be of the InnoDB engine (actually, if both tables are of the MyISAM engine, this error will not occur at all, but the foreign key will not be generated, only the index will be created) You need to check The engine type of the table.

4. The name of the foreign key cannot be repeated. You should check your database to make sure the foreign key names are unique, or you can add a few random characters after the key name to test if this is the cause.

5. You may have set ON DELETE SET NULL, but the related key fields are set to NOTS NULL values. You may solve the problem by modifying the cascade attribute value or setting the field attribute to allow null.

6. Please make sure 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, such as default=0.

8. There are syntax errors in the ALTER statement

When an error occurs, carefully check the design of the database tables and fields. I also recommend that everyone try to use tools to design and create databases, which can reduce the probability of errors.

Recommended learning: "MySQL Video Tutorial"

The above is the detailed content of What to do if mysql fails to add foreign key. For more information, please follow other related articles on the PHP Chinese website!