CODEWITHSUNDEEP
sql-servert-sql
user3043278
user3043278

Reputation: 184

Delete rows from a table in Sql Server

How can I delete a row from a table if it has a foreign key? I have this stored procedure but when I execute it it gives me this error :The DELETE statement conflicted with the REFERENCE constraint "FK__Pilot_Fli__pilot__4E88ABD4". The conflict occurred in database "Airline Reservation", table "dbo.Pilot_Flight", column 'pilot_id'.

create procedure DeletePilot
    (@id INTEGER,@result varchar(70) output)
as
    If NOT Exists ( Select * From Pilot
            Where pilot_id=@id) 
    Begin
        Set @result='There is no record with that ID'
        RETURN
    END

    Delete from Pilot
    where pilot_id=@id
    set @result='Record was deleted'
    RETURN
GO
select * from Pilot
Declare @res varchar(70)
EXEC DeletePilot 7,@res OUTPUT
print(@res)

Can anyone help me please!

Upvotes: 0

Views: 1348

Answers (2)

Andy
Andy

Reputation: 55

There are records in dbo.Pilot_Flight that reference records in dbo.Pilot.

You could delete the records in Pilot_Flight before deleting the records in Pilot, enable (cascade delete which would delete records in Pilot_Flight when Pilot records are deleted (bad), or disable the foreign key reference... (worse).

Upvotes: 1

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

You'd have to either run a statement like this (if it's nullable):

UPDATE Pilot_Flight
SET pilot_id = NULL
WHERE pilot_id = @id

or do this:

DELETE Pilot_Flight WHERE pilot_id = @id

Either way you have to do one or the other before the DELETE from Pilot.

Upvotes: 2

Related Questions