'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.

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.

Solution 1:^[1]

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.

Solution 2:^[2]

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

Solution 3:^[3]

When you are referring one column of one table to another table using a foreign key Make sure that the column you are referring to should either a primary key or it should be unique. then use

ALTER TABLE table_name1
ADD CONSTRAINT constraint_name
FOREIGN KEY(column_name_in_table_name1) 
REFERENCES table_name2(column_name_in_table_name2);

this will do for you.

Solution 4:^[4]

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.

Solution 5:^[5]

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.

Solution 6:^[6]

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.

Solution 7:^[7]

I am also getting the same error while learning SQL. Later all the tried possible ways, I found that we must enable the primary key of referred table than it will enable the foreign key. Hope it will helpful.

-- Foreign key add and remove

SHOW DATABASES;
USE test;
DESCRIBE addresses;
DESCRIBE people;

ALTER TABLE people
ADD CONSTRAINT FK_PeopleAddress
FOREIGN KEY (address_id) REFERENCES addresses(id);

Solution 8:^[8]

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.

Solution 9:^[9]

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

Solution 10:^[10]

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!

Solution 11:^[11]

I received this error in mysql workbench during a forward engineer. After double checking the indexes and fk's were correct (Unique or PK vals), I was able to solve this by selecting "Skip creation of FOREIGN KEYS" and "Skip Creation of FK Indexes as well".

I was then getting a successful forward engineer with the options above selected. After a successful forward engineer, try running it a second time with those options deselected (default).

This did the trick for me.

Solution 12:^[12]

If anyone is coming to this stackoverflow question with MySQL error code 1822, and still getting the same error even after keeping the same data type in the parent and child table.

Then please make sure that the source table table1's column which is getting referred in table2 is indexed / declared as the primary key or unique.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2	John Rausch
Solution 3	Mohit kumar
Solution 4	Carlos Laspina
Solution 5	Gordon Linoff
Solution 6	Juan
Solution 7	marc_s
Solution 8	Rishi Latchmepersad
Solution 9	Gachuru Peter
Solution 10	Ryan Sacks
Solution 11	joe mama
Solution 12	nomadSK25

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

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]

Solution 7:[7]

Solution 8:[8]

Solution 9:[9]

Solution 10:[10]

Solution 11:[11]

Solution 12:[12]