MySQL error: ERROR: Error 1822: Failed to add the foreign key constraint. Missing index for constrai-CodePudding

I found a couple of threads with the same error but they didn't work for me. I am trying to add foriegn keys to the database but it keeps throwing me this error even after I add the primary key to the reference table

CREATE TABLE `Company_list` (
  `Company_id` int NOT NULL AUTO_INCREMENT,
  `CompanyName` text,
  `Total_Employees` int DEFAULT NULL,
  `Company_Emails` text,
  `Money_owe` float DEFAULT NULL,
  PRIMARY KEY (`Company_id`),
  UNIQUE KEY `Company_id_UNIQUE` (`Company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `Employee_Location_Log` (
  `location_log_id` int NOT NULL AUTO_INCREMENT,
  `EntryDate` datetime DEFAULT NULL,
  `ExitDate` datetime DEFAULT NULL,
  `Facility_list_Facility_ID` int NOT NULL,
  PRIMARY KEY (`location_log_id`,`Facility_list_Facility_ID`),
  KEY `fk_Employee_Location_Log_Facility_list1_idx` (`Facility_list_Facility_ID`),
  CONSTRAINT `fk_Employee_Location_Log_Facility_list1` FOREIGN KEY (`Facility_list_Facility_ID`) REFERENCES `Facility_list` (`Facility_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `Employees` (
  `Employee_id` int NOT NULL AUTO_INCREMENT,
  `FirstName` text,
  `LastName` text,
  `Email_Address` text,
  `Password` text,
  `Company_list_Company_id` int NOT NULL,
  PRIMARY KEY (`Employee_id`,`Company_list_Company_id`),
  UNIQUE KEY `Employee_id_UNIQUE` (`Employee_id`),
  KEY `fk_Employees_Company_list_idx` (`Company_list_Company_id`),
  CONSTRAINT `fk_Employees_Company_list` FOREIGN KEY (`Company_list_Company_id`) REFERENCES `Company_list` (`Company_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `Facility_Bookings` (
  `Booked_Entry_Date` datetime NOT NULL,
  `Booked_Exit_Date` datetime NOT NULL,
  `Facility_list_Facility_ID` int NOT NULL,
  `Facility_list_Facility_ID1` int NOT NULL,
  `Booking_id` int NOT NULL,
  PRIMARY KEY (`Booking_id`,`Booked_Entry_Date`,`Booked_Exit_Date`),
  KEY `fk_Facility_Bookings_Facility_list1_idx` (`Facility_list_Facility_ID`),
  KEY `fk_Facility_Bookings_Facility_list2_idx` (`Facility_list_Facility_ID1`),
  CONSTRAINT `fk_Facility_Bookings_Facility_list1` FOREIGN KEY (`Facility_list_Facility_ID`) REFERENCES `Facility_list` (`Facility_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_Facility_Bookings_Facility_list2` FOREIGN KEY (`Facility_list_Facility_ID1`) REFERENCES `Facility_list` (`Facility_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `Facility_employee_access` (
  `Facility_list_Facility_ID` int NOT NULL,
  `Employees_Employee_id` int NOT NULL,
  `Facility_Bookings_Booked_Entry_Date` datetime NOT NULL,
  `Facility_Bookings_Booked_Exit_Date` datetime NOT NULL,
  KEY `fk_Facility_employee_access_Facility_list1_idx` (`Facility_list_Facility_ID`),
  KEY `fk_Facility_employee_access_Facility_Bookings2_idx` (`Facility_Bookings_Booked_Entry_Date`,`Facility_Bookings_Booked_Exit_Date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `Facility_list` (
  `Facility_ID` int NOT NULL AUTO_INCREMENT,
  `FacilityType` text,
  PRIMARY KEY (`Facility_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

I am altering 2 tables in my database:

 ALTER TABLE `Companies`.`Facility_employee_access` 
        ADD CONSTRAINT `fk_Facility_employee_access_Facility_list1`
          FOREIGN KEY (`Facility_list_Facility_ID`)
          REFERENCES `Companies`.`Facility_list` (`Facility_ID`)
          ON DELETE CASCADE
          ON UPDATE CASCADE,
        ADD CONSTRAINT `fk_Facility_employee_access_Employees1`
          FOREIGN KEY (`Employees_Employee_id`)
          REFERENCES `Companies`.`Employees` (`Employee_id`)
          ON DELETE CASCADE
          ON UPDATE CASCADE,
        ADD CONSTRAINT `fk_Facility_employee_access_Facility_Bookings2`
          FOREIGN KEY (`Facility_Bookings_Booked_Entry_Date` , `Facility_Bookings_Booked_Exit_Date`)
          REFERENCES `Companies`.`Facility_Bookings` (`Booked_Entry_Date` , `Booked_Exit_Date`)
          ON DELETE CASCADE
          ON UPDATE CASCADE

AND:

ALTER TABLE `Companies`.`Facility_Bookings` 
ADD INDEX `fk_Facility_Bookings_Facility_list1_idx` (`Facility_list_Facility_ID` ASC) VISIBLE,
ADD INDEX `fk_Facility_Bookings_Facility_list2_idx` (`Facility_list_Facility_ID1` ASC) VISIBLE,
DROP INDEX `fk_Facility_Bookings_Facility_list2_idx` ,
DROP INDEX `fk_Facility_Bookings_Facility_list1_idx` ;
;

When I run the query I get this error:

ERROR: Error 1822: Failed to add the foreign key constraint. Missing index for constraint 'fk_Facility_employee_access_Facility_Bookings2' in the referenced table 'Facility_Bookings'

I think the issue is this part of the query:

  ADD CONSTRAINT `fk_Facility_employee_access_Facility_Bookings2`
          FOREIGN KEY (`Facility_Bookings_Booked_Entry_Date` , `Facility_Bookings_Booked_Exit_Date`)
          REFERENCES `Companies`.`Facility_Bookings` (`Booked_Entry_Date` , `Booked_Exit_Date`)
          ON DELETE CASCADE
          ON UPDATE CASCADE

But I already have a primary key inside the "Facility_Bookings table" So i'm confused to why I am still getting this error when I try to add the forgien key.

CodePudding user response：

You refer to Facility_list table in the foreign key definitions in Employee_Location_Log and Facility_Bookings tables. But this table not exists yet, its creation code is posessed below.

So you must simply reorder the tables creation and put Facility_list creation code before Employee_Location_Log and Facility_Bookings creation code. In this case all tables are created successfully.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d237c114320ca867e65b33e136cacddc