Mysql workbench ошибка 1822

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 ?

create table TruckMake(
TruckMakeID char(3) NOT NULL,
TruckMakeName varchar(20) NOT NULL,
primary key (TruckMakeID));

create table TruckModel(
TruckMakeID char(3) NOT NULL,
TruckModelID char(3) NOT NULL,
TruckModelName varchar(5) NOT NULL,
primary key (TruckMakeID, TruckModelID),
foreign key (TruckMakeID) references TruckMake(TruckMakeID));

create table Truck(
TruckVINNum char(4) NOT NULL,
TruckMakeID char(3) NOT NULL,
TruckModelID char(3) NOT NULL,
TruckColour varchar(15) NOT NULL,
TruckPurchaseDate date NOT NULL,
TruckCost decimal(8,2) NOT NULL,
primary key (TruckVINNum),
constraint truck_ibfk_2 foreign key (TruckMakeID) references TruckModel(TruckMakeID),
constraint truck_ibfk_1 foreign key (TruckModelID) references TruckModel(TruckModelID));

The error i’m getting is:

01:19:32 create table Truck( TruckVINNum char(4) NOT NULL, TruckMakeID char(3) NOT NULL, TruckModelID char(3) NOT NULL, TruckColour varchar(15) NOT NULL, TruckPurchaseDate date NOT NULL, TruckCost decimal(8,2) NOT NULL, primary key (TruckVINNum), constraint truck_ibfk_2 foreign key (TruckMakeID) references TruckModel(TruckMakeID),

constraint truck_ibfk_1 foreign key (TruckModelID) references TruckModel(TruckModelID)) 
Error Code: 1822. Failed to add the foreign key constraint.  
   Missing index for constraint 'truck_ibfk_1' in the referenced table 'truckmodel' 0.000 sec

How do i fix this?

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