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.
Advertisement
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 );