错误代码:1822.无法添加外键约束.缺少约束索引

本文介绍了错误代码:1822.无法添加外键约束.缺少约束索引的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我发现了一些有关该错误的线索.但是所有解决方案都不适合我.

I found some threads about the error. But all the solutions doesn't work for me.

我为用户表创建了2个表，为文章创建了1个表.现在，我要存储创建文章的用户和最后一个修饰符的用户.

I created 2 tables a user table and one for articles. Now I want to store the user that created the article and the one who is the last modifier.

CREATE TABLE IF NOT EXISTS `testDb`.`users` ( `id` INT NOT NULL AUTO_INCREMENT, `nickname` VARCHAR(255) NULL, `first_name` VARCHAR(255) NULL, `last_name` VARCHAR(255) NULL, `e_mail` VARCHAR(255) NOT NULL, `activated` TINYINT(1) NOT NULL DEFAULT 0, `birth_date` DATE NULL, `locked` TINYINT(1) NOT NULL DEFAULT 0, `locked_date_time` DATETIME NULL, `street` VARCHAR(255) NULL, `street_number` VARCHAR(255) NULL, `city` VARCHAR(255) NULL, `postal_code` VARCHAR(255) NULL, `country` VARCHAR(255) NULL, `phone` VARCHAR(255) NULL, PRIMARY KEY (`id`), UNIQUE INDEX `user_id_UNIQUE` (`id` ASC) ) ENGINE = InnoDB AUTO_INCREMENT = 1; CREATE TABLE IF NOT EXISTS `testDb`.`articles` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NULL, `description` VARCHAR(255) NULL, `create_user` INT ZEROFILL NOT NULL, `create_date_time` DATETIME NULL, `last_modifie_user` INT ZEROFILL NOT NULL, `last_modifie_date_time` DATETIME NULL, PRIMARY KEY (`id`), UNIQUE INDEX `article_id_UNIQUE` (`id` ASC), INDEX `fk_articles_users1_idx` (`create_user` ASC), INDEX `fk_articles_users2_idx` (`last_modifie_user` ASC) ) ENGINE = InnoDB AUTO_INCREMENT = 1; ALTER TABLE `testDb`.`articles` ADD CONSTRAINT `fk_articles_users1` FOREIGN KEY (`create_user`) REFERENCES `testDb`.`users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_articles_users2` FOREIGN KEY (`last_modifie_user`) REFERENCES `testDb`.`users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

我收到以下错误，但我不明白为什么要为此添加索引.

I get the following error, but I didn't understand why I should have a index for that.

错误代码:1822.无法添加外键约束.引用表"users"中约束"fk_articles_users1"的缺少索引

Error Code: 1822. Failed to add the foreign key constaint. Missing index for constraint 'fk_articles_users1' in the referenced table 'users'

我活跃

SHOW ENGINE innodb STATUS;

但这不会显示任何错误.

but this doesn't shows any erros.

推荐答案

create_user INT UNSIGNED ZEROFILL无法引用id INT，因为出于外键引用的目的，它们被视为不同的数据类型.使它们具有相同的数据类型.

create_user INT UNSIGNED ZEROFILL cannot reference id INT, because these count as different data types for purposes of foreign key reference. Make them the same data type.

外键关系中列之间唯一允许的数据类型差异是varchar的长度.例如，VARCHAR(10)可以引用VARCHAR(20)，反之亦然.

The only data type difference that is permitted between columns in a foreign key relationship is length of a varchar. For example, VARCHAR(10) can reference VARCHAR(20) or vice-versa.

数据类型，大小或字符集的任何其他差异对于引用完整性都是不兼容的.

Any other difference in data type, size, or character set is incompatible for referential integrity.

即使一列上有ZEROFILL，但另一列上没有，也会导致数据类型不兼容.

Even having ZEROFILL on one column but not the other makes the data types incompatible.