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

Question

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.

Unclasp

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. – Mortie

I tried it without the UNIQUE, still the same problem. – Unclasp

. . 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. – Mortie

Sorry, you are right. I've mixed some codes together. I edit it. – Unclasp

Who voted the question down? =( Is still unsolved und it seems not one of the common mistakes like different datatype – Unclasp

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

Bookout

Thanks Bill, I dont have it in mind that ZEROFILL 'changes' the datatype. I expected that it is something like a create-trigger. Now its working! – Unclasp

For me, it was UNSIGNED property. In Sequal Pro this field looks like this bit.ly/21uNJmT – Ftc

Answer 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

Unsuspected

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

Hettie

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

Hypnotherapy

Answer 5

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.

Grocer

Answer 6

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.

Mortie

Answer 7

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.

Westsouthwest

In my case, I imported the model from another database and some tables was with Collation utf8_general_ci and others latim_swedish_cli. – Felloe

Answer 8

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);

Baptistry

Answer 9

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.

Seamus

Answer 10

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

Henton

Please add some explanation to your answer such that others can learn from it – Thumping

Answer 11

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!

Poverty

Answer 12

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.

Dabber

Answer 13

For me, I don't have a primary key and I am applying foreign key constrained(), so make sure to check your primary exists.

Broadbrim

Answer 14

Same issue! but solved. I was referring to the same table while creating a foreign key.

foreign key(id)references same_table(id),

That was the reason for the error.

Nob

Answer 15

In the case where your foreign key is comprised of multiple columns, you will see this issue if the order of the columns in the child table's foreign key declaration does not match the order of the columns in the parent table's index.

CREATE TABLE parent_table(
  foo INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  bar INT NOT NULL,
  -- Order must match this unique constraint.
  CONSTRAINT ux_parent_table UNIQUE(foo, bar)
);

CREATE TABLE child_table(
  bar INT NOT NULL,
  foo INT NOT NULL,
  -- Error, order does not match parent.
  CONSTRAINT fk_error FOREIGN KEY(bar, foo) REFERENCES parent_table(bar, foo),
  -- Correct, order matches parent.
  CONSTRAINT fk_correct FOREIGN KEY(bar, foo) REFERENCES parent_table(bar, foo)
);

Note that it is specifically the order of the columns in the parent table's index that matters. The order of the columns in the parent table itself doesn't matter.

Losing