mySQL foreign key error

Question

I'm making a database for a school project in mySQL server and, for some reason, foreign keys won't establish; every iteration of the syntax I attempt gives me an error saying it's wrong, and links to a page not found. I've checked my notes, class material, and w3schools for the syntax, and theoretically it's correct, but it refuses to acknowledge or implement.

I cannot for the life of me figure out what it is I'm missing or not getting. The variable types match, they have the same length, and they're both set to not null. I don't understand what I have or haven't done that's causing this.

This is the error I get:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`romanos`.`indivres`, CONSTRAINT `indivres_ibfk_1` FOREIGN KEY (`resID`) REFERENCES `reservation` (`reservationID`))

This is the code I used to make the table I'm trying to reference:

CREATE TABLE Reservation (
 reservationID INT(6) NOT NULL PRIMARY KEY,
  date DATETIME NOT NULL,
  type char(10) NOT NULL,
  guestname varchar(30) NOT NULL,
  guestnumber INT(2) NOT NULL,
  guestphone varchar(10) NOT NULL
 );
 
 INSERT INTO Reservation
 VALUES 
 (001234,'2025-02-14 17:30', 'Individual', 'Keys', 2, '824-345-1266'),
  (043320,'2024-10-13 16:00', 'Banquet', 'Rodriguez', 15, '824-345-1266'),
  (057344,'2025-01-23 16:30', 'Banquet', 'Langston & Co', 10, '824-345-1266'),
  (004255,'2024-11-03 17:15', 'Individual', 'Soper-Smith', 2, '824-345-1266'),
  (003210,'2024-12-17 18:00', 'Individual', 'Irving', 2, '824-345-1266');

And this is the code that keeps giving me the error in my current attempt:

CREATE TABLE IndivRes (
 indivID INT(6) NOT NULL,
 resID INT(6) NOT NULL,
 seatingpref varchar(20),
 smoking char(3),
 PRIMARY KEY (indivID),
 FOREIGN KEY (resID) REFERENCES reservation(reservationID)
); 
INSERT INTO IndivRes
VALUES
 (001121, 001234, 'By the window', 'No'),
 (002213, 004255, 'Booth', 'No'),
 (001644, 12279, 'Outside', 'Yes'),
 (001684, 003210, 'Near the bar', 'No');
INSERT INTO IndivRes (indivID)
 VALUES (933341);```

• Craig: You understand what the foreign key is actually enforcing? Every value that you try to put into the IndivRes.resID column must already exist as a value in Reservation.reservationID column. Are you sure that is definitely the case? (Based on the specific example you gave, that is definitely not the case - eg. your example doesn't show 12279 or 933341 being inserted as "reservationID" values in the "Reservation" table
• Akina: INSERT INTO IndivRes (indivID) Is not possible on your structure - you do not set the value for resID which is NOT NULL and does not have default value.

Answer 1

Your second insert statement into IndivRes

INSERT INTO IndivRes (indivID)
VALUES (933341);```

is not valid because you aren't supplying a value for resID. It's defined as a NOT NULL column so you can't insert into that table without providing a value for that column. Also as you've defined the resID column as a foreign key then you'd need to make sure that any value you do insert for resID already exists in the Reservation table as a reservationID