错误代码:1822.添加外键约束失败.缺少约束索引 [英] Error Code: 1822. Failed to add the foreign key constaint. Missing index for constraint

问题描述

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

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.

这篇关于错误代码:1822.添加外键约束失败.缺少约束索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！