Introduction: MySQL often needs to create a constraint between parent and child tables. This constraint needs to be based on the primary and foreign key. Here, a problem encountered in the process of creating the primary and foreign key constraint is solved.
1. Question raising
Create two tables:
Product: sealer: supplier list
The corresponding SQL is as follows:
product:

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;

 

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 for the parent-child table primary foreign key association.

2. Encountering a mistake
The SQL and error message used to create the foreign key is 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 encountered is as follows:

Unable to insert foreign key constraint correctly.
3. Problem analysis
The primary foreign key is more about the primary key of a table being associated with a column of a child table, which requires the same data type and attributes. Will this be a problem?
Requirements: Same data types and constraints
Unsigned. The character length of the number is not consistent.
4. Solutions
Modify the data type in the product.sid, add unsigned and the length of the field, and set it to the same.

5. To summarize
The problem of 1215 is due to the inconsistency of data types between the primary and foreign keys. Similar problems in the future can be handled accordingly.