Mysql reasons for adding foreign key errors: 1. The field of the foreign key does not match the type of the associated field; 2. The foreign key name is repeated; 3. The foreign key is not indexed; 4. The foreign key has a default value ;5. Grammatical errors.
Cause analysis:
(Recommended tutorial: mysql video tutorial)
First type Situation: The type of the foreign key field does not match the type of the associated field (including mismatch in encoding format)
Second situation: The name of the foreign key is an existing key value, and the name must be unique
The third situation: the foreign key creation caused by the mysql engine cannot be saved or cannot function. The general default engine of mysql is myisam, and myisam cannot create foreign keys.
The fourth situation: the foreign key you are trying to create is not indexed, or is not a primary key. If it is not a primary key, you must create an index for it.
Fifth situation: The action of the foreign key is set to on delete set null or on update set null, but the field of the associated table is set to no null, causing a conflict.
The sixth situation: In the table primary key set by navicat, there are differences between primary key 1, primary key 2, and primary key 3. Primary key 1 cannot be cascade updated (deleted) (CASCADE)
Seventh situation: The conflict caused by the existence of a default value in the foreign key
The eighth situation: The situation caused by the lack of index for the mixed key value, you need to apply for a separate index for it
The ninth situation Situation: When creating a foreign key in a sql statement, create or alter syntax error
Related recommendations:mysql tutorial
The above is the detailed content of What is the reason for the error in adding foreign keys to mysql?. For more information, please follow other related articles on the PHP Chinese website!