CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Hunain Hafeez
Hunain Hafeez

Reputation: 187

How to not to delete if record exists as a foreign in another table?

I have 2 tables. If I delete a record from table1 then first query should check if it's pk exists as a foreign key in table2 then it should not delete the record else it should.

I used this but throwing syntax error 

DELETE FROM Setup.IncentivesDetail
INNER JOIN Employee.IncentivesDetail ON Setup.IncentivesDetail.IncentivesDetailID = Employee.IncentivesDetail.IncentiveDetail_ID
WHERE Setup.IncentivesDetail.IncentivesDetailID= @IncentivesDetailID
    AND Employee.IncentivesDetail.IncentiveDetail_ID= @IncentivesDetailID

UPDATE:

Based on the answers below I have done this, is it correct ?

If Not Exists(Select * from Employee.IncentivesDetail where IncentivesDetail.IncentiveDetail_ID= @IncentivesDetailID)
        Begin
            Delete from Setup.IncentivesDetail
            WHERE Setup.IncentivesDetail.IncentivesDetailID= @IncentivesDetailID
        End
        Else
        Begin
            RAISERROR('Record cannot be deleted because assigned to an employee',16,1) 
            RETURN  
        End

Upvotes: 4

Views: 868

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82504

What you are describing is the definition of the foreign key constraint.
If you already have a foreign key between these tables, make sure it's not marked as ON DELETE CASCADE.
If it is, you should delete it and re-create it without that ON DELETE CASCADE see this link from MSDN for details.
If you don't already have a foreign key constraint, you need to create one:

ALTER TABLE Setup.IncentivesDetail 
ADD CONSTRAINT FK_Setup_IncentivesDetail_IncentivesDetailID FOREIGN KEY (IncentivesDetailID) 
    REFERENCES Employee.IncentivesDetail (IncentiveDetail_ID ) 
;

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Maybe like this?

DELETE FROM Setup.IncentivesDetail
WHERE Setup.IncentivesDetail.IncentivesDetailID= @IncentivesDetailID
  AND NOT EXISTS(SELECT 1 FROM Employee.IncentivesDetail WHERE IncentiveDetail_ID= @IncentivesDetailID)

But I must admit, this smells a bit... Are you doing a clean up or ist this something you are doing regularely?

Upvotes: 2

Related Questions