When a foreign key is added to MySQL, the following error occurs: 1215 Cannot add the foreign key constraint, 1215 constraint

When a foreign key is added to MySQL, the following error occurs: 1215 Cannot add the foreign key constraint, 1215 constraint

Preface

This article mainly covers ERROR 1215 (HY000): Cannot add foreign key constraint when creating a table, if you are interested in the same problem, refer.

I. Question proposal

Create two tables:

Product: product table

Sealer: Supplier table

The corresponding SQL statement is as follows:

Product table:

DROP TABLE IF EXISTS `product`; CREATE TABLE `product` (  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(20) NOT NULL COMMENT 'product name',  `price` float(10,3) NOT NULL,  `description` varchar(20) DEFAULT NULL,  `count` int(11) NOT NULL DEFAULT '0',  `sid` int(11) NOT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `id_index` (`id`) USING HASH,  UNIQUE KEY `sid_index` (`sid`) USING HASH ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Sealer table:

DROP TABLE IF EXISTS `sealer`; CREATE TABLE `sealer` (  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(30) NOT NULL,  `city` varchar(255) DEFAULT NULL,  `created_time` datetime DEFAULT NULL,  `updated_time` datetime DEFAULT NULL,  `level` int(11) NOT NULL DEFAULT '0',  `description` varchar(40) DEFAULT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `id_index_1` (`id`) USING HASH ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; 

Next, we need to associate product. sid to sealer. id to associate the primary and Foreign keys of the Parent and Child tables.

Ii. Errors

When creating a foreign key, the SQL statement used and the error message are as follows:

alter table `product' add CONSTRAINT `sid_ref` FOREIGN KEY (`sid`) REFERENCES `sealer` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION 

The error message is as follows:

The foreign key constraint cannot be correctly inserted.

3. Problem Analysis

The primary and Foreign keys are more associated with a table's primary key and a column in the sub-table. They must have the same data type and attributes. Will the problem occur here?

Requirements: have the same data types and constraints

It is found that: unsigned, numbers have different character lengths.

4. Solution

Modify the Data Type in product. sid, add the length of unsigned and field, and set it to the same.

Summary

1215 of the problem occurs because the data types between the primary and Foreign keys are inconsistent. In the future, similar problems can be handled according to this. The above is all about this article. I hope this article will help you in your study or work. If you have any questions, please leave a message. Thank you for your support.