Sql ошибка 1822 - Решение и исправление самых разных ошибок на 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.

Источник

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 ?

Источник

Я создавал базу данных, создавая 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. Тем не менее, возникает та же проблема.

Источник

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.

Источник

When working with MySQL databases, you may encounter the error message “MySQL Error 1822: Failed to add foreign key constraint; missing index for constraint BUT index exists”. This error message usually occurs when trying to add a foreign key constraint to a table but the index for the constraint is missing. In this guide, we will discuss how to troubleshoot and resolve this error message.

Understanding Foreign Key Constraints

A foreign key constraint is a way to enforce referential integrity between two tables. It ensures that the values in a column of one table (the child table) match the values in a column of another table (the parent table). In MySQL, you can create a foreign key constraint by using the `FOREIGN KEY` keyword.

Here is an example of creating a foreign key constraint in MySQL:

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
);

In this example, the `orders` table has a foreign key constraint on the `customer_id` column that references the `customer_id` column in the `customers` table.

Troubleshooting MySQL Error 1822

If you encounter the “MySQL Error 1822: Failed to add foreign key constraint; missing index for constraint BUT index exists” error message, it means that you are trying to add a foreign key constraint to a table but the index for the constraint is missing.

However, in some cases, you may have already created an index for the column that the foreign key constraint references. In this case, you need to ensure that the index is created correctly and has the right properties.

Here is an example of creating an index for a column in MySQL:

CREATE INDEX idx_customer_id ON customers (customer_id);

To troubleshoot the “MySQL Error 1822” error message, follow these steps:

Check that the index exists for the column that the foreign key constraint references. You can do this by running the following SQL command:

SHOW INDEX FROM {table_name} WHERE Column_name = '{column_name}';

Replace `{table_name}` with the name of the table that contains the column and `{column_name}` with the name of the column that the foreign key constraint references.

Check that the index has the correct properties. The index should have the same data type as the column that the foreign key constraint references. If the data types do not match, you will need to recreate the index.
Check that the index is not disabled. If the index is disabled, you can enable it by running the following SQL command:

ALTER TABLE {table_name} ENABLE KEYS;

Replace `{table_name}` with the name of the table that contains the index.

Resolving MySQL Error 1822

To resolve the “MySQL Error 1822” error message, you need to create the missing index for the column that the foreign key constraint references.

Here is an example of creating an index for a column in MySQL:

CREATE INDEX idx_customer_id ON customers (customer_id);

Once the index is created, you can try adding the foreign key constraint again. If the error message persists, follow the troubleshooting steps mentioned above.

Conclusion

In this guide, we discussed how to troubleshoot and resolve the “MySQL Error 1822: Failed to add foreign key constraint; missing index for constraint BUT index exists” error message. We covered the basics of foreign key constraints in MySQL and provided code examples to help you create an index and troubleshoot the error message. With these tips, you should be able to resolve the error and ensure referential integrity in your MySQL databases.

Источник