MYSQL failed to establish a foreign key several situations record Can' est create table could not create a table
- 2020-05-10 23:02:20
- OfStack
Cannot create like this one. frm file error seems to suggest that the operating system file permissions error or other reasons, but in fact, these are not, in fact, the mysql error has been reported is a mysql bug itself and appear in mysql developers list for many years, however, it seems that this is one kind of misleading.
In many instances, the happening of this kind of mistake because mysql1 straight since cannot very good support, the problem of the relationship between more unfortunately it didn't specify what a problem can cause the above error, below I put in a common cause of this terrible 150 error list, and I made order, the size of the possibility to known the reason:
For example, if one is INT(10), the foreign key must be INT(10), not INT(11) and not 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 integer. In addition, you must also determine whether two fields is one for SIGNED, while the other one is UNSIGNED, these two fields must strictly 1 to match, more information about signed and unsigned, please refer to: http: / / www verysimple. com blog /? p = 57
2. One of the foreign keys you are trying to reference is not indexed, or is not an primary key. If one of the foreign keys is not an primary key, you must create an index for it.
3. The name of the foreign key is an existing key value. At this time, you should check your database to make sure that the foreign key name is unique to 1, or you should add a few random characters after the key name to test whether this is the reason.
4. If one or two of the tables are from the MyISAM engine, you must use the InnoDB engine if you want to use the foreign key constraint (in fact, if both tables are from the MyISAM engine, this error will not happen at all, but no foreign keys will be generated), you can set the engine type of the table by querying the browser
5. You may have set ON DELETE SET NULL, but the relevant key field is set to NOTS NULL. You can fix bug by changing the property value of cascade or by setting the field property to allow null.
6. Make sure your Charset and Collate options are 1 to 1 at the table and field levels
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, it does not have its own independent index, at this point, you must create a separate index for it.
9. There are grammatical errors in ALTER declaration
Finally: it's a bit of a hassle to create a foreign key, you can directly generate ddl using Myeclipse...
In many instances, the happening of this kind of mistake because mysql1 straight since cannot very good support, the problem of the relationship between more unfortunately it didn't specify what a problem can cause the above error, below I put in a common cause of this terrible 150 error list, and I made order, the size of the possibility to known the reason:
For example, if one is INT(10), the foreign key must be INT(10), not INT(11) and not 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 integer. In addition, you must also determine whether two fields is one for SIGNED, while the other one is UNSIGNED, these two fields must strictly 1 to match, more information about signed and unsigned, please refer to: http: / / www verysimple. com blog /? p = 57
2. One of the foreign keys you are trying to reference is not indexed, or is not an primary key. If one of the foreign keys is not an primary key, you must create an index for it.
3. The name of the foreign key is an existing key value. At this time, you should check your database to make sure that the foreign key name is unique to 1, or you should add a few random characters after the key name to test whether this is the reason.
4. If one or two of the tables are from the MyISAM engine, you must use the InnoDB engine if you want to use the foreign key constraint (in fact, if both tables are from the MyISAM engine, this error will not happen at all, but no foreign keys will be generated), you can set the engine type of the table by querying the browser
5. You may have set ON DELETE SET NULL, but the relevant key field is set to NOTS NULL. You can fix bug by changing the property value of cascade or by setting the field property to allow null.
6. Make sure your Charset and Collate options are 1 to 1 at the table and field levels
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, it does not have its own independent index, at this point, you must create a separate index for it.
9. There are grammatical errors in ALTER declaration
Finally: it's a bit of a hassle to create a foreign key, you can directly generate ddl using Myeclipse...