Click here to Skip to main content
15,562,222 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi, I'm getting this error
Failed to add the foreign key constraint. Missing index for constraint 'RowID' in the referenced table 'room_row_seat'	


	CREATE TABLE IF NOT EXISTS `prime_cinema_Final`.`tickets1`
 (   `TicketID` VARCHAR(50) NOT NULL,
   `Price` INT NOT NULL, 
  `MovieID` VARCHAR(20) NOT NULL, 
  `Show_Time` TIME NOT NULL, 
  `Show_Date` DATE NOT NULL, 
  `EmployeeID` VARCHAR(20) NOT NULL, 
  `RoomID` INT NOT NULL, 
  `RowID` VARCHAR(20) NOT NULL, 
  `SeatID` INT NOT NULL, 
  `CustomerID` VARCHAR(20) NOT NULL, 
  `Payment_Method` VARCHAR(20) NULL DEFAULT NULL,
   PRIMARY KEY (`TicketID`),
   INDEX `EmployeeID_idx` (`EmployeeID` ASC) VISIBLE, 
  INDEX `CustomerID_idx` (`CustomerID` ASC) VISIBLE,
   INDEX `RoomID_idx` (`RoomID` ASC) VISIBLE,
   INDEX `RowID_idx` (`RowID` ASC) VISIBLE, 
  INDEX `SeatID_idx` (`SeatID` ASC) VISIBLE,
   INDEX `MovieID_idx` (`MovieID` ASC) VISIBLE, 
  INDEX `Show_Time_idx` (`Show_Time` ASC) VISIBLE,
   INDEX `Show_Date_idx` (`Show_Date` ASC) VISIBLE, 
  CONSTRAINT `EmployeeID`     FOREIGN KEY (`EmployeeID`)     REFERENCES `prime_cinema_Final`.`employees` (`EmployeeID`), 
  CONSTRAINT `CustomerID`     FOREIGN KEY (`CustomerID`)     REFERENCES `prime_cinema_Final`.`customers` (`CustomerID`)     ON DELETE NO ACTION     ON UPDATE NO ACTION,  
 CONSTRAINT `RoomID`     FOREIGN KEY (`RoomID`)     REFERENCES `prime_cinema_Final`.`room_row_seat` (`RoomID`)     ON DELETE NO ACTION     ON UPDATE NO ACTION,  
 CONSTRAINT `RowID`     FOREIGN KEY (`RowID`)     REFERENCES `prime_cinema_Final`.`room_row_seat` (`RowID`)     ON DELETE NO ACTION     ON UPDATE NO ACTION,  
 CONSTRAINT `SeatID`     FOREIGN KEY (`SeatID`)     REFERENCES `prime_cinema_Final`.`room_row_seat` (`SeatID`)     ON DELETE NO ACTION     ON UPDATE NO ACTION,  
 CONSTRAINT `MovieID`     FOREIGN KEY (`MovieID`)     REFERENCES `prime_cinema_Final`.`movie_timings` (`MovieID`)     ON DELETE NO ACTION     ON UPDATE NO ACTION,   
CONSTRAINT `Show_Time`     FOREIGN KEY (`Show_Time`)     REFERENCES `prime_cinema_Final`.`movie_timings` (`Show_Time`)     ON DELETE NO ACTION     ON UPDATE NO ACTION,   
CONSTRAINT `Show_Date`     FOREIGN KEY (`Show_Date`)     REFERENCES `prime_cinema_Final`.`movie_timings` (`Show_Date`)     ON DELETE NO ACTION     ON UPDATE NO ACTION,   
CONSTRAINT `RoomID`     FOREIGN KEY (`RoomID`)     REFERENCES `prime_cinema_Final`.`movie_timings` (`RoomID`)     ON DELETE NO ACTION     ON UPDATE NO ACTION) 
ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci	


What I have tried:

Movie_timings table has
RoomId,MovieId,Show_Date and Show_Time as primary key (4 are primary)  --RoomId here is foreign for another table and MovieId is foreign for another table

room_row_seat table has
RoomId,RowId,SeatId as primary key (3 are primary)     --- RoomId here is foreign for another table
Posted
Updated 17-Oct-22 0:41am
v2
Comments
Join wick 17-Oct-22 5:47am    
My name is Join Wick. I work as a Digital marketer from 2 Year in different companies but currently work in Tricksehow
. I love work on Blogging, Software & Apps but currently work on Windows.

1 solution

A foreign key constrain needs to point to the primary key of the foreign table.

The RowID column is not the primary key of the room_row_seat table. Instead, you have a composite primary key, consisting of the RoomId, RowId, and SeatId columns.

Therefore, your FK relationship needs to use all three columns to refer to the foreign table.

Those need to be part of a single FK relationship, not three separate FK relationships as you currently have.
SQL
/* Remove these:
CONSTRAINT `RoomID`     FOREIGN KEY (`RoomID`)     REFERENCES `prime_cinema_Final`.`room_row_seat` (`RoomID`)     ON DELETE NO ACTION     ON UPDATE NO ACTION,  
 CONSTRAINT `RowID`     FOREIGN KEY (`RowID`)     REFERENCES `prime_cinema_Final`.`room_row_seat` (`RowID`)     ON DELETE NO ACTION     ON UPDATE NO ACTION,  
 CONSTRAINT `SeatID`     FOREIGN KEY (`SeatID`)     REFERENCES `prime_cinema_Final`.`room_row_seat` (`SeatID`)     ON DELETE NO ACTION     ON UPDATE NO ACTION,  
*/

/* Add this: */
CONSTRAINT `FK_tickets1_seat` FOREIGN KEY (`RoomID`, `RowID`, `SeatID`) REFERENCES `prime_cinema_Final`.`room_row_seat` (`RoomID`, `RowID`, `SeatID`) ON DELETE NO ACTION ON UPDATE NO ACTION,

NB: You'll almost certainly have the same problem with the FK relationship to the movie_timings table - you're currently trying to define four separate relationships to that table, rather than a single FK relationship based on a composite primary key.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900