CODEWITHSUNDEEP
sql-servert-sql
user3043278
user3043278

Reputation: 184

Procedure not working

create table Train_Types
(
id int not null primary key,
name varchar(50) not null
)

create table Trains
(
id int not null primary key,
name varchar(50) not null,
trainTypeId int not null foreign key references Train_Types(id)
)

create table Stations
(
id int not null primary key,
name varchar(50) not null
)

create table Routs
(
id int not null primary key,
name varchar(50) not null,
trainId int not null foreign key references Trains(id)
)

create table Routes_Stations
(
stationId int not null foreign key references Stations(id),
routeId int not null foreign key references Routs(id),
arrivalDate datetime not null,
departureDate datetime not null
)


create procedure addStation @stationId int, @routeId int, @arrivalTime datetime,
                            @departureTime datetime
as
begin
insert into Routes_Stations (stationId,routeId,arrivalDate,departureDate)
values (@stationId, @routeId, @arrivalTime, @departureTime)
end
go

So here I basically have a train-schedule database . I tried to make a procedure that receives a station, a route , an arrival and a departure time and adds this new station to the route. My problem is that when I write this 

exec addStation 1,4,'18:00','18:05'

I get this error :

Msg 547, Level 16, State 0, Procedure addStation, Line 5
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Routes_St_stati_1DE57479". The conflict occurred in database "Train Schedule", table "dbo.Stations", column 'id'.

I don't understand what it says. Can someone help me fix it please I'm new to databases

Now that I fixed my problem I try to modify this procedure so that if the station that I want to insert already exists I just update the arrival and departure time . So far I wrote this but I'm stuck , I don't know how to assign the total nr. of stations to a variable.

create procedure addStation @stationId int, @routeId int, @arrivalTime datetime,
                            @departureTime datetime
as
begin
declare @isStation_inRoute int
set @isStation_inRoute = 0

select *from Routes_Stations where stationId = @stationId
and routeId = @routeId
@isStation_inRoute = count(*)
insert into Routes_Stations (stationId,routeId,arrivalDate,departureDate)
values (@stationId, @routeId, @arrivalTime, @departureTime)
end
go

Upvotes: 1

Views: 69

Answers (1)

marc_s
marc_s

Reputation: 754368

The error says: you're trying to insert a row into dbo.Routes_Stations with a value for StationId that doesn't exist in the referenced dbo.Stations parent table.

It seems you're trying to insert a stationId = 1 value - and judging from the error, that Station with that Id value doesn't exist.

This behavior is the whole point of foreign key constraints: you cannot insert data that doesn't match the FK relationships. Those would be "ghost records", routes pointing to a station that doesn't exist. You need to make sure to insert only valid values into your foreign key column!

Upvotes: 3

Related Questions