MySQL establishes foreign key failure cant create table several situations record

Source: Internet
Author: User
Tags mysql tutorial

This error occurs because the MySQL tutorial has always been unable to support the problem of relationships, unfortunately, it does not indicate which problem is causing the above error, and I list the common causes of this terrible 150 error, and I sort the size of the probabilities, Known reasons:

1, two fields of type or size do not exactly match, for example, if one is int (10), then the foreign key must also be set to int (10), not int (11) nor can it be 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 integers. In addition, you must also determine whether two fields are one signed and the other is unsigned, which must be closely matched, and for more information about signed and unsigned, see: Http://

2, you are trying to reference one of the foreign keys did not establish an index, or not a primary key, if one of them is not primary key, you must create an index for it.

3, the name of the foreign key is an already existing a key value, this time, you should check your database tutorial to ensure that the name is unique, or you add a few random characters after the key name to test whether this is the reason.

4, one or two tables are MyISAM engine tables, if you want to use a FOREIGN key constraint, you must be a InnoDB engine, (in fact, if two tables are MyISAM engines, this error does not occur at all, but it does not produce foreign keys), you can set the engine type of the table by querying the browser

5, you may set the on DELETE set NULL, but the related key field is set to the NOTS null value. You can fix the bug by modifying the Cascade property value or by setting the field property to allow null.

6, please make sure that your charset and Collate options are consistent at the table level and field level

7, you may set the foreign key set a default value, such as Default=0

8, in this relationship, one of the fields is one of the mixed key values, which does not have its own independent index, at which point you must create a separate index for it.

9. There are grammatical errors in ALTER statement

Finally: the establishment of foreign keys is a bit troublesome, you can directly use Myeclips Tutorial E to generate DDL ...

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.