Cause of error:
1. The type or size of the two fields do not strictly match. For example, if one of them is int (10), then the foreign key must also be set to int (10), not int (11), and it cannot be tinyint. You can use the show command to view the size of the field, because some query browsers sometimes display int (10) and int (11) as integer. In addition, it is necessary to confirm whether both fields are signed or not, and the two fields must be strictly matched all the time.
2. When an attempt is made to refer to one of the foreign keys, which is not indexed or is not primary key, an index must be created for this foreign key.
3. The name of a foreign key is an existing key value. You should make sure that the foreign key name is unique, or add several characters randomly after the key name to test whether this is the reason.
4. If you want to use foreign key constraints, one or two of the tables representing the MyISAM engine must be InnoDB engine (if both tables are MyISAM engines, this error will not occur at all, but foreign keys will not be generated).
5. It is possible that on delete set null is set, but the related key field is set to not null. You can fix this bug by modifying the property value of cascade or setting the field property to allow null.
6. Make sure your charset and collate options are consistent at the table and field levels.
7. A default value may be set for the foreign key, such as default = 0
8. In this relationship, one of the fields is one of the mixed key values. It does not have its own independent index, so it is necessary to create an independent index for it.
9. Error in alert declaration.
10. The two tables to be joined have different encoding formats.
Refer to Baidu document: https://wenku.baidu.com/view/7a0a1f1b10a6f524ccbf85e6.html
Read More:
- MySQL error: can’t create table ‘..’（ errno:150 ）Solutions
- About error 1005 (HY000) in MySQL: can’t create table ‘_______ ‘(errno: 150) fool’s plan
- SQL exception handling – MySQL error 1005 (HY000): can’t create table ‘TMP’ (errno: 13)
- MySQL – ERROR 1146 (42S02): Table ‘mysql.user’ doesn’t exist
- DB2 create table error – 104 42601 illegal symbol encountered in SQL statement
- MySQL partitions the existing tables of the data table
- Question: cannot create windows service for mysql.error : 0 (install MySQL)
- mysql Error Code: 1022. Can’t write; duplicate key in table `xxx`
- mysql ERROR 1050 (42S01): Table already exists
- Mysql error when deleting the table structure: Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint fails
- An error was reported in the process of importing the table from mysql5.7: [err] 1067 – invalid default value for ‘***‘
- mysql Table ‘performance_ schema.session_ Variables’ doesn’t exist solution
- 【Exception】mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table…
- Insufficient table space ORA-00604 unable to extend table SYS.AUD by 8192
- Global lock, table lock and row lock in MySQL
- 1093 – You can’t specify target table ‘table’ for update in FROM clause
- An error occurred when opensips started the service“ ERROR:core :db_ check_ table_ Version: invalid version 7 for Table dialog found “
- Error 1146 (42s02): table ‘database name. Table name’ doesn’t exist ‘
- [MySQL] error 1396 (HY000): Operation create user failed for ‘MySQL’ @’localhost ‘
- (element UI component table) how to add a style to a table