CODEWITHSUNDEEP
sqlsql-server
Agnes
Agnes

Reputation: 67

Delete from 2 tables with foreign key constraints

I have 2 tables tlbinvoice and tblRel_Inv_Course in which InvoiceID is the foreign key. When I tried to delete a row from the Invoice table, I get an error

Cannot delete foreign key constraint

Below are the 2 queries and data:

 select * from  invoice where InvoiceID=19

    InvoiceID   invimagetype  location
    -----------------------------------
    19          image/jpeg    network


 select * from  Rel_Inv_Course where CourseID=4262 

    Rel_I_C_ID  CourseID    InvoiceID
    ----------------------------------
    2255        4262            19

What I tried: 

delete from [TAP].[dbo].Invoice 
where InvoiceID = (select InvoiceID 
                   from Rel_Inv_Course 
                   where CourseID = 4262)

delete from Rel_Inv_Course 
where CourseID = 4262

But I can't do this. I need to delete from both the rows of the tables with invoice id as 19. Please help.

Upvotes: 0

Views: 1517

Answers (2)

Thom A
Thom A

Reputation: 96044

This might be easier to explain with some sample data and DDL:

USE Sandbox;
GO

CREATE TABLE dbo.Parent (ID int NOT NULL,
                         SomeString varchar(100) NOT NULL);
GO
CREATE TABLE dbo.Child (ID int NOT NULL,
                        ParentID int NOT NULL,
                        AnotherString varchar(100) NOT NULL);
GO
ALTER TABLE dbo.Parent ADD CONSTRAINT PK_PID PRIMARY KEY CLUSTERED (ID);

ALTER TABLE dbo.Child ADD CONSTRAINT PK_CID PRIMARY KEY CLUSTERED (ID);

ALTER TABLE dbo.Child
ADD CONSTRAINT FK_PID
    FOREIGN KEY (ParentID)
    REFERENCES dbo.Parent (ID);
GO

INSERT INTO dbo.Parent (ID,
                        SomeString)
VALUES (1, 'sdfkgjbhasdfg'),
       (2, 'sdfkjsdbhkf');
GO
INSERT INTO dbo.Child (ID,
                       ParentID,
                       AnotherString)
VALUES (1, 1, 'asdfkiashjbd'),
       (2, 1, '#asldjasbhdk,'),
       (3, 2, 'asfjasdfj');
GO

--Try to delete a row in Parent:
DELETE FROM dbo.Parent
WHERE ID = 2;
--No surprise it failed
GO
--Try to delete a row in child
DELETE FROM dbo.Child
WHERE ID = 2;
--This worked fine.
GO
--
--If we check, however, ParentID 1 and 2 are still in the table:
SELECT *
FROM dbo.Child;
--We want to delete ID 1 in parent, so we need to delete the other row
DELETE FROM dbo.Child
WHERE ParentID = 1;
--Now delete in Parent
DELETE FROM dbo.Parent
WHERE ID = 1;
GO
DROP TABLE dbo.Child;
DROP TABLE dbo.Parent;

You'll notice that the first delete on Parent failed, as it conflicts with the foreign key constraint. After, however, deleting all the rows in child for that ID, you can delete the parent row.

The same logic applies with your data. Delete the relevant rows in the child table first, and then you delete the data in your parent table. Alternatively, implement cascading, and then you simply need to delete the row in the parent, and the deletes will cascade down.

Upvotes: 0

Ian-Fogelman
Ian-Fogelman

Reputation: 1605

As the comments said all you need to do is flip your delete statements and you should be good:

You may consider wraping them in a begin tran so you can check that your deletes only delete the data you want as well:

  Begin Tran
    DECLARE @INVOICEID INT
    SET @INVOICE = (select InvoiceID from Rel_Inv_Course where CourseID=4262)

        delete from Rel_Inv_Course where CourseID=4262

        delete from [TAP].[dbo].Invoice where InvoiceID =(@INVOICEID)

    --Select * from Rel_Inv_Course 
    --Select * from [dbo].Invoice 

    --If satisfied with deletes finally commit tran
    --If not satisfied --> Rollback Tran
    Commit Tran

Upvotes: 4

Related Questions