Reputation: 583

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.

Upvotes: 40

Answers (15)

codestothestars

Reputation: 31

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.

Upvotes: 0

Muhammad Noman

Reputation: 21

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.

Upvotes: 0

Hadayat Niazi

Reputation: 2480

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

Upvotes: 0

Vighnesh

Reputation: 11

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

Upvotes: 1

nomadSK25

Reputation: 2594

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.

Upvotes: 5

joe mama

Reputation: 1

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.

Upvotes: 0

Mohit kumar

Reputation: 81

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.

Upvotes: 8

Ryan Sacks

Reputation: 524

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!

Upvotes: 0

Gachuru Peter

Reputation: 1

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

Upvotes: 0

Rishi Latchmepersad

Reputation: 684

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.

Upvotes: 0

ccov77

Reputation: 791

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.

Upvotes: 1

Carlos Laspina

Reputation: 2231

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.

Upvotes: 5

John Rausch

Reputation: 186

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

Upvotes: 9

Bill Karwin

Reputation: 562981

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.

Upvotes: 31

Gordon Linoff

Reputation: 1271171

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.

Upvotes: 1

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

Answers (15)

Related Questions