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

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

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

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

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

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.