MySQL 错误代码：1822。无法添加外键约束。缺少约束索引

Error Code: 1822. Failed to add the foreign key constaint. Missing index for constraint

提问by Trival

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

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

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.

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

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.

我收到以下错误，但我不明白为什么我应该有一个索引。

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

错误代码：1822。无法添加外键约束。引用表 'users' 中缺少约束 'fk_articles_users1' 的索引

I actived

我激活了

SHOW ENGINE innodb STATUS;

but this doesn't shows any erros.

但这并没有显示任何错误。

回答by Bill Karwin

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

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

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.

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

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

数据类型、大小或字符集的任何其他差异都与参照完整性不兼容。

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

即使只有ZEROFILL一列而不是另一列，也会使数据类型不兼容。

回答by John Rausch

I came across this issue and my Data Type was correct so I was stumped for a little but then I just made everything the same.

我遇到了这个问题，我的数据类型是正确的，所以我被难住了一点，但后来我把一切都做了一样。

When creating foreign keys be sure the columns you are using have the same:

创建外键时，请确保您使用的列具有相同的：

• Data Type
• Collation
• Zero Fill
• Not Null
• Unsigned
• Binary

• 数据类型
• 整理
• 零填充
• 不为空
• 未签名
• 二进制

回答by Carlos Laspina

You could use SHOW FULL COLUMNS FROM table_namewhich returns a column Collation, for example for a table accountswith a special collation on the column name

您可以使用SHOW FULL COLUMNS FROM table_namewhich 返回列Collat​​ion，例如对于列accounts上具有特殊排序规则的表name

mysql> SHOW FULL COLUMNS FROM accounts;
+----------+--------------+-------------------+------+-----+---------+----------+
| Field    | Type         | Collation         | Null | Key | Default | Extra    |
+----------+--------------+-------------------+------+-----+---------+----------|
| id       | int(11)      | NULL              | NO   | PRI | NULL    | auto_inc |
| name     | varchar(255) | utf8_bin          | YES  |     | NULL    |          |
| email    | varchar(255) | latin1_swedish_ci | YES  |     | NULL    |          |
...

Both columns have to has the same collation.

两列必须具有相同的排序规则。

To change the collation of column

要更改列的排序规则

ALTER TABLE t1 MODIFY
    col1 VARCHAR(5)
      CHARACTER SET latin1
      COLLATE latin1_swedish_ci;

It's happened to me.

它发生在我身上。

回答by Gordon Linoff

There must be some subtle problem in the alter tablestatement. Changing the definition of articlesfixes the problem:

alter table声明中一定有一些微妙的问题。更改定义articles可解决问题：

CREATE TABLE IF NOT EXISTS `articles` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL,
  `description` VARCHAR(255) NULL,
  `create_user` INT ZEROFILL NOT NULL REFERENCES users(id),
  `create_date_time` DATETIME NULL,
  `last_modifie_user` INT ZEROFILL NOT NULL REFERENCES users(id),
  `last_modifie_date_time` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_articles_users1_idx` (`create_user` ASC),
  INDEX `fk_articles_users2_idx` (`last_modifie_user` ASC)
)
ENGINE = InnoDB
AUTO_INCREMENT = 1;

Hereis the SQL Fiddle.

这是 SQL 小提琴。

回答by Juan

In my case, the error was that I didn't know that the name of the FKs have to be unique across the whole database. Renaming the FK fixed the issue.

就我而言，错误在于我不知道 FK 的名称在整个数据库中必须是唯一的。重命名 FK 解决了这个问题。