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

23
votes

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.

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'

I actived 

SHOW ENGINE innodb STATUS;

but this doesn't shows any erros.

8
mysqlsqlforeign-keys
If you declare id to be the primary key, you do not need a separate unique index. My guess is that having both confuses MySQL. - Gordon Linoff
I tried it without the UNIQUE, still the same problem. - Trival
. . You claim to have two tables, but the code references three. There is no recipes -- making it really hard to determine what the problem is. - Gordon Linoff
Sorry, you are right. I've mixed some codes together. I edit it. - Trival
Who voted the question down? =( Is still unsolved und it seems not one of the common mistakes like different datatype - Trival

8 Answers

23
votes

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.

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.

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

6
votes

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
3
votes

You could use SHOW FULL COLUMNS FROM table_name which returns a column Collation, for example for a table accounts with a special collation on the column 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.

1
votes

There must be some subtle problem in the alter table statement. Changing the definition of articles fixes the problem:

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;

Here is the SQL Fiddle.

1
votes

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.

0
votes

For me the issue was that my default collation was different in my database. Ensure that your default collation for your database matches the collation of the fields that your FK is trying to reference.

0
votes

Ensure the parent table is not a child of another table.

0
votes

For some reason, ->unsignedBigInteger() didn't work for me.

So I changed it slightly from this: $table->unsignedBigInteger('owner_id');

To this: $table->bigInteger('owner_id')->unsigned();

And now it works!