Ошибка 1822 mysql - Решение и исправление самых разных ошибок на TopOshibok.ru

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.

Источник

The «Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint» is a common error message encountered when working with MySQL database management system. This error message indicates that there is a problem with creating a foreign key constraint in a database table. The reason for the error message is usually due to a missing index on the referenced columns in the parent table. In order to resolve this error, it’s important to understand the root cause of the issue and take the necessary steps to fix it.

Method 1: Create Index on Referenced Columns in Parent Table

To fix the «Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint» in MySQL, you can create an index on the referenced columns in the parent table. Here’s how to do it in a few steps:

Identify the parent table and the referenced columns in the child table.

For example, if you have a parent table called «users» with a primary key column «id», and a child table called «orders» with a foreign key column «user_id» that references the «id» column in the «users» table, you would identify «users.id» and «orders.user_id» as the referenced columns.

Create an index on the referenced columns in the parent table.

To create the index, you can use the CREATE INDEX statement with the following syntax:

CREATE INDEX index_name
ON parent_table (referenced_column1, referenced_column2, ...)

For example, to create an index on the «id» column in the «users» table, you would use the following statement:

CREATE INDEX idx_users_id
ON users (id)

Try adding the foreign key constraint again.

Once you’ve created the index, you can try adding the foreign key constraint again using the ALTER TABLE statement with the following syntax:

ALTER TABLE child_table
ADD CONSTRAINT constraint_name
FOREIGN KEY (foreign_key_column)
REFERENCES parent_table (referenced_column)

For example, to add a foreign key constraint on the «user_id» column in the «orders» table that references the «id» column in the «users» table, you would use the following statement:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id)
REFERENCES users (id)

If everything is done correctly, the foreign key constraint should be added successfully without any errors.

Here’s the complete code example:

-- Create the parent table
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

-- Create an index on the referenced column in the parent table
CREATE INDEX idx_users_id
ON users (id);

-- Create the child table
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  amount DECIMAL(10,2),
  CONSTRAINT fk_orders_users
  FOREIGN KEY (user_id)
  REFERENCES users (id)
);

Note that the order in which you create the tables and the foreign key constraints may affect the outcome. In general, it’s a good practice to create the parent table and the index on the referenced column(s) first, and then create the child table and the foreign key constraint.

Method 2: Verify Data Types of Referenced Columns

One possible solution to fix the «Mysql: how to fix Error Code: 1822. Failed to add the foreign key constaint. Missing index for constraint?» error is to verify the data types of referenced columns. Here are the steps to follow:

Check the data types of the columns involved in the foreign key constraint. Make sure they match exactly, including the length and precision if applicable.

DESCRIBE table1;
DESCRIBE table2;

If the data types don’t match, you can either change the data type of the referencing column or the referenced column to make them match. Here’s an example of changing the data type of the referencing column:

ALTER TABLE table1 MODIFY COLUMN col1 INT UNSIGNED;

If the data types match, check if the referenced column has an index. If it doesn’t, create an index on the referenced column.

ALTER TABLE table2 ADD INDEX idx_col2 (col2);

Finally, create the foreign key constraint using the ALTER TABLE statement.

ALTER TABLE table1 ADD CONSTRAINT fk_col1 FOREIGN KEY (col1) REFERENCES table2 (col2);

By following these steps, you should be able to fix the «Mysql: how to fix Error Code: 1822. Failed to add the foreign key constaint. Missing index for constraint?» error by verifying the data types of referenced columns.

Method 3: Check for Duplicate Data in Referenced Columns

To fix the «Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint?» in MySQL, you can check for duplicate data in referenced columns. Here’s how to do it:

Identify the table that has the foreign key constraint by running the following query:

SHOW CREATE TABLE table_name;

Identify the referenced column in the foreign key constraint.
Run the following query to check for duplicate data in the referenced column:

SELECT referenced_column, COUNT(*) FROM referenced_table GROUP BY referenced_column HAVING COUNT(*) > 1;

If there is duplicate data in the referenced column, remove the duplicates by running the following query:

ALTER IGNORE TABLE referenced_table ADD UNIQUE INDEX index_name (referenced_column);

Try adding the foreign key constraint again.

Here’s an example of how to check for duplicate data in the «user_id» column of the «orders» table:

-- Identify the table and referenced column
SHOW CREATE TABLE orders;

-- Check for duplicate data
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id HAVING COUNT(*) > 1;

-- Remove duplicates
ALTER IGNORE TABLE orders ADD UNIQUE INDEX user_id_unique (user_id);

By following these steps, you should be able to fix the «Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint?» issue in MySQL.

Method 4: Use ALTER TABLE Statement to Add Foreign Key Constraint

To add a foreign key constraint using the ALTER TABLE statement in MySQL, follow these steps:

First, identify the table and column(s) that will serve as the foreign key. In this example, we will use a table called orders with a foreign key column called customer_id.
Ensure that the referenced table and column(s) exist and have the correct data types. In this example, we will assume that the customers table has a primary key column called id with a data type of INT.
Create an index on the foreign key column(s). This is required before adding the foreign key constraint. In this example, we will create an index on the customer_id column:

ALTER TABLE orders ADD INDEX fk_orders_customers_idx (customer_id);

Add the foreign key constraint using the ALTER TABLE statement. In this example, we will add a foreign key constraint that references the customers table:

ALTER TABLE orders ADD CONSTRAINT fk_orders_customers
  FOREIGN KEY (customer_id)
  REFERENCES customers(id);

The complete code example is as follows:

-- Step 1: Identify the table and column(s) that will serve as the foreign key
-- In this example, we will use a table called `orders` with a foreign key column called `customer_id`
USE mydatabase;
 
-- Step 2: Ensure that the referenced table and column(s) exist and have the correct data types
-- In this example, we will assume that the `customers` table has a primary key column called `id` with a data type of `INT`
 
-- Step 3: Create an index on the foreign key column(s)
-- This is required before adding the foreign key constraint
-- In this example, we will create an index on the `customer_id` column
ALTER TABLE orders ADD INDEX fk_orders_customers_idx (customer_id);
 
-- Step 4: Add the foreign key constraint using the ALTER TABLE statement
-- In this example, we will add a foreign key constraint that references the `customers` table
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers
  FOREIGN KEY (customer_id)
  REFERENCES customers(id);

This should resolve the «Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint?» issue when adding a foreign key constraint in MySQL.

Method 5: Increase the Size of Referenced Columns

To fix the «Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint?» issue in MySQL, you can increase the size of the referenced columns. Here are the steps to do this:

Identify the table and column that is causing the issue. For example, let’s assume the table name is «orders» and the column name is «customer_id».
Check the data type and size of the column in the «orders» table. For example, if the data type is «INT» and the size is «11», you can increase the size to «20» by running the following command:
```
ALTER TABLE orders MODIFY COLUMN customer_id INT(20);
```
Check the data type and size of the column in the referenced table. For example, if the referenced table is «customers» and the column name is «id», you can check the data type and size by running the following command:
```
SHOW COLUMNS FROM customers WHERE Field = 'id';
```
If the data type and size of the referenced column is smaller than the modified column in the «orders» table, you need to increase the size of the referenced column as well. For example, if the data type is «INT» and the size is «11», you can increase the size to «20» by running the following command:
```
ALTER TABLE customers MODIFY COLUMN id INT(20);
```
Once you have increased the size of both columns, you can add the foreign key constraint by running the following command:
```
ALTER TABLE orders ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers(id);
```

That’s it! By increasing the size of the referenced columns, you should be able to fix the «Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint?» issue in MySQL.

Источник

I’m trying to add a foreign key with 2 columns.

Here is the DDL of table referencing the foreign key :

CREATE TABLE IF NOT EXISTS `sf_file_category` (
  `id_file_category`        INT                         NOT NULL AUTO_INCREMENT,
  `name`                    VARCHAR(45)
                            CHARACTER SET 'latin1'
                            COLLATE 'latin1_general_ci' NOT NULL,
  `file_type`               ENUM('document', 'image', 'video', 'archive')
                            CHARACTER SET 'latin1'
                            COLLATE 'latin1_general_ci' NULL,
  `id_file_category_parent` INT UNSIGNED                NULL,
  PRIMARY KEY (`id_file_category`),
  INDEX `fk_sf_file_category_sf_file_category1_idx` (`id_file_category_parent` ASC),
  INDEX `fk_sf_file_category_sf_file_idx` (`id_file_category` ASC, `file_type` ASC)
)
  ENGINE = InnoDB;

DDL of table who owns the foreign key :

CREATE TABLE IF NOT EXISTS `sf_file` (
  `id_file`           INT UNSIGNED                NOT NULL AUTO_INCREMENT,
  `fullpath`          VARCHAR(100)
                      CHARACTER SET 'latin1'
                      COLLATE 'latin1_general_ci' NOT NULL,
  `basename`          VARCHAR(45)
                      CHARACTER SET 'latin1'
                      COLLATE 'latin1_general_ci' NOT NULL,
  `accesskey`         CHAR(8)
                      CHARACTER SET 'latin1'
                      COLLATE 'latin1_general_ci' NOT NULL,
  `file_type`         ENUM('document', 'image', 'video', 'archive')
                      CHARACTER SET 'latin1'
                      COLLATE 'latin1_general_ci' NULL,
  `name`              VARCHAR(45)
                      CHARACTER SET 'latin1'
                      COLLATE 'latin1_general_ci' NULL,
  `description`       VARCHAR(255)
                      CHARACTER SET 'latin1'
                      COLLATE 'latin1_general_ci' NULL,
  `id_aircraft_image` SMALLINT UNSIGNED           NULL,
  `id_aircraft`       SMALLINT UNSIGNED           NULL,
  `id_file_category`  INT UNSIGNED                NULL,
  PRIMARY KEY (`id_file`),
  INDEX `fk_sf_file_sf_file_category1_idx` (`id_file_category` ASC, `file_type` ASC),
  INDEX `fk_sf_file_sf_aircraft1_idx` (`id_aircraft` ASC),
  INDEX `fk_sf_file_sf_aircraft2_idx` (`id_aircraft_image` ASC)
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = latin1
  COLLATE = latin1_general_ci;

Trying to execute following foreign key syntax :

ALTER TABLE `sf_file` 
ADD CONSTRAINT `fk_sf_file_sf_file_category1`
  FOREIGN KEY (`id_file_category` , `file_type`)
  REFERENCES `sf_file_category` (`id_file_category` , `file_type`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

But I get this error :
ERROR: Error 1822: Failed to add the foreign key constaint. Missing index for constraint ‘fk_sf_file_sf_file_category1’ in the referenced table ‘sf_file_category’.

I assume he means the INDEX fk_sf_file_sf_file_category1_idx (id_file_category ASC, file_type ASC) that is already created in the table sf_file_category.

Is there any particular way for creating multiple field foreign key that I am missing ?

Источник

Вопрос:

Я пытаюсь добавить внешний ключ к моей таблице расписания полетов, но он терпит неудачу, но я действительно не знаю, почему. Внешний ключ должен ссылаться на атрибут txtAC_tag из таблицы tblAircraft, который является частью основного ключа! Таким образом, tblAircraft индексируется (первичный ключ – это комбинированный ключ, который состоит из idAC и txtAC_tag → может ли проблема с объединенным Первичным ключом?), И тип данных атрибута соответствует.

Вот мои объявления таблиц и объявления внешнего ключа:

create table if not exists tblAircrafts(
idAC       int not null auto_increment,
txtAC_tag  varchar(255) not null,
txtAC_type varchar(255) not null,
primary key(idAC, txtAC_tag));

create table if not exists tblFlightSchedule(
ID int not null auto_increment,
datDate date,
txtFrom varchar(255),
txtTo   varchar(255),
txtFlight varchar(255),
numFlight_time_decimal decimal(4,2),
txtAC_tag varchar(255) not null,
txtAC_type varchar(255) not null,
numSeatCapacity int unsigned,
numLoad int unsigned, -- auslastung
numDirt decimal(20,19),
numTotalFlightTime decimal(50,5),
numCumDirt decimal(20,15),
primary key(ID));

alter table tblflightschedule
add foreign key(txtAC_tag) references tblaircrafts(txtAC_tag);

И вот сообщение ERROR:

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

Какие-либо предложения? Я ценю любую помощь, которую вы можете мне дать, спасибо!

Лучший ответ:

Проблема здесь:

add foreign key(txtAC_tag) references tblaircrafts(txtAC_tag);

здесь вы привязываете txtAC_tag к txtAC_tag таблицы tblaircrafts но в tblaircrafts столбец txtAC_tag является ни unique ни primary, почему он показывает ошибку.

Для отношения внешних ключей столбец родительских таблиц, на котором вы создаете отношение, должен быть unique или primary и они должны иметь одинаковый тип данных.

Чтобы устранить это, сделайте столбец txtAC_tag уникальным.

Ответ №1

Кажется, вы создали Composite Primary Key для таблицы tblAircrafts.

Если вы хотите добавить ссылку на составной ключ в таблицу tblflightschedule, вам нужно использовать следующий синтаксис:

alter table tblflightschedule
add foreign key ('int Column', txtAC_tag) references tblaircrafts **(idAC, txtAC_tag);**

И вам нужно передать два столбца для добавления внешнего ключа (‘int Column’, txtAC_tag).

Таким образом, вы можете добавить еще один столбец в таблицу tblflightschedule или удалить один столбец из таблицы tblaircrafts.

Источник

Я создавал базу данных, создавая 3 таблицы (classes, lectures, taking), а затем изменяя таблицу, чтобы добавить foreign key.

Однако я продолжаю получать следующую ошибку: ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint

Я не вижу никаких проблем с кодами, так в чем может быть проблема?? (Я использовал mySQL Workbench. Я пытался копировать и вставлять в виде txt-файла, потому что я думал, что это может быть проблема с сопоставлением, но все же возникала та же проблема)

Код выглядит следующим образом:

CREATE TABLE classes (
    course_id VARCHAR(8),
    classes_id VARCHAR(10),
    semester VARCHAR(10),
    year VARCHAR(10),
    PRIMARY KEY (course_id, classes_id, semester, year)
);

CREATE TABLE taking (
    student_id VARCHAR(8),
    course_id VARCHAR(10),
    classes_id VARCHAR(10),
    semester VARCHAR(10),
    year VARCHAR(10),
    grade char(1),
    PRIMARY KEY (student_id, course_id, semester, year)
);

CREATE TABLE lectures (
    professor_id VARCHAR(8),
    course_id VARCHAR(10),
    classes_id VARCHAR(10),
    semester VARCHAR(10),
    year VARCHAR(10),
    PRIMARY KEY (professor_id, course_id, semester, year)
);

ALTER TABLE taking ADD CONSTRAINT consTAKE3 FOREIGN KEY(classes_id) REFERENCES classes(classes_id) ON DELETE CASCADE;
ALTER TABLE taking ADD CONSTRAINT consTAKE4 FOREIGN KEY(semester) REFERENCES classes(semester) ON DELETE CASCADE;
ALTER TABLE taking ADD CONSTRAINT consTAKE5 FOREIGN KEY(year) REFERENCES classes(year) ON DELETE CASCADE;

ALTER TABLE lectures ADD CONSTRAINT consLEC3 FOREIGN KEY(classes_id) REFERENCES classes(classes_id) ON DELETE CASCADE;
ALTER TABLE lectures ADD CONSTRAINT consLEC4 FOREIGN KEY(semester) REFERENCES classes(semester) ON DELETE CASCADE;
ALTER TABLE lectures ADD CONSTRAINT consLEC5 FOREIGN KEY(year) REFERENCES classes(year) ON DELETE CASCADE;

Ошибка будет выглядеть следующим образом:

ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'consTAKE3' in the referenced table 'classes'
ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'consTAKE4' in the referenced table 'classes'
ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'consTAKE5' in the referenced table 'classes'
ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'consLEC3' in the referenced table 'classes'
ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'consLEC4' in the referenced table 'classes'
ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'consLEC5' in the referenced table 'classes'

Прямо сейчас я пытаюсь выполнить это из cmd, используя ysql –u root –p. Тем не менее, возникает та же проблема.

Источник