Mysql add foreign key times wrong: 1215 cannot add the FOREIGN KEY constraint solution _mysql

Source: Internet
Author: User
Tags datetime hash

Objective

This article mainly involves in the data creation table, encountered error 1215 (HY000): Cannot add foreign key constraint problem aspects of the content, for the data creation table, encounter the same problem interested students can refer to.

First, the question of the proposed

Create two tables:

Product: Product List

Sealer: Supplier list

The corresponding SQL is as follows:

Product table:

DROP TABLE IF EXISTS ' product '; 
CREATE TABLE ' product ' ( 
 ' id ' bigint () unsigned not null auto_increment, 
 ' name ' varchar NOT NULL COMMENT ' PR Oduct name ', ' Price 
 ' float (10,3) is not NULL, 
 ' description ' varchar DEFAULT null, 
 ' count ' int (one) not null D Efault ' 0 ', 
 ' sid ' Int (one) not NULL, 
 PRIMARY key (' id '), 
 unique key ' Id_index ' (' id ') USING HASH, 
 unique K EY ' Sid_index ' (' Sid ') USING HASH 

Sealer Table:

DROP TABLE IF EXISTS ' sealer '; 
CREATE TABLE ' sealer ' ( 
 ' id ' bigint () unsigned not null auto_increment, 
 ' name ' varchar (#) NOT NULL, 
 ' city ' varchar (255) default NULL, 
 ' created_time ' datetime default NULL, 
 ' updated_time ' datetime default NULL, 
 ' Level ' int (one) not null default ' 0 ', 
 ' description ' varchar default NULL, 
 PRIMARY key (' id '), 
 UNIQUE key ' Id_index_1 ' (' id ') USING HASH 

Next we need to correlate product.sid to sealer.id for the primary foreign Key Association of the parent-child table.

Second, encountered errors

When you create a foreign key, the SQL and encountered error messages that you use are as follows:

 
 

The error message encountered is as follows:


The FOREIGN KEY constraint cannot be inserted correctly.

3, problem analysis

The primary foreign key is more of a table's primary key associated with a column of the child table, the requirement is to have the same data type and attributes, the problem will not appear here?

Requirements: Have the same data types and constraints

Discovery: Unsigned, the character length of the number is inconsistent.

4. Solutions

Modify the data type in the Product.sid, add the unsigned and the length of the field, and set it to the same.

Summarize

The problem with 1215 occurs because of inconsistent data types between primary foreign keys, which can be handled in future similar problems. The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring certain help, if you have questions you can message exchange. Thank you for your support to the cloud-dwelling community.

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: info-contact@alibabacloud.com 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.