ERROR 1452: Cannot add or update a child row: a foreign key constraint fails, Can anybody assist

I was wondering if anybody would be so kind as to try help me figure out th problem with my code. I have an extremially limited knowledge of sql I am a student and this is my first year doing sql.

Here is the first table containing the model as the primary key.

create table Train(
Model int primary key auto_increment,
fullname varchar(40),
OriginStation varchar(80),
destination varchar (80),
TrainNo varchar (15),
PassengerCode varchar (20) not null,
TrackID int,
foreign key (TrackID) references Tracks(TrackID) on update cascade on delete cascade
);

Then I have used this as a foreign key in a few other tables and had no issues, but when I tried to use it as a foreign key inside this table this is where the issue occurs.

create table Trainseats(
seatno int primary key auto_increment, 
Model int,
PassengerCode int,
foreign key (Model) references Train(Model) on update cascade on delete cascade,
foreign key (PassengerCode) references Passenger(PassengerCode) on update cascade on delete cascade
);

This is the error Im facing. 12:09:39 insert into Trainseats values( 530, 2000, 100 ) Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (railway.trainseats, CONSTRAINT trainseats_ibfk_2 FOREIGN KEY (PassengerCode) REFERENCES passenger (PassengerCode) ON DELETE CASCADE ON UPDATE CASCADE) 0.016 sec

If anybody could shine a light on this issue it would be much appreciated.

Answer

This is your code:

insert into Trainseats
    values( 530, 2000, 100 );

You have left out the columns! So it is interpreted as:

insert into Trainseats (seatno, Model, PassengerCode)
    values( 530, 2000, 100 );

The error is saying that PassengerCode = 100 does not exist in the Passenger table.

Note: Normally, you do not insert into auto increment columns. And you should always explicitly list the columns:

insert into Trainseats (Model, PassengerCode)
    values( 2000, 100 );

Advertisement

Answer