Failed to add the foreign key constraint. Missing index for constraint ' in the referenced table ''

Question

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 15-Oct-22 23:52pm

Member 14800672

Updated 16-Oct-22 23:41pm

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 2022-10-16T23:41:00

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.