delete FK Table Data when passing in the PK of another table

Question

I am calling a HttpDelete API to delete data from my DB via a stored proc.

I have a table called CustomerQueryData like below (lots of columns removed for brevity.

   CustomerQueryId | CustId | CarId | CustName
    1                   1       1       Joe
    2                   2       2       Jane

Then I have a Car Table like below (CarId on the customerquery data table is a FK to the car table)

CarId | CarName
1        BMW
2        Audi

In the stored PROC I am getting passed The CustId and I need to delete all data associated with that CustId. If I try to delete the Data from CarTable first based on the CustId using a join I can't due to FK constraint. If I delete the CustomerData first based on the ID I pass in it will delete from that table but then I will have an orphaned row in the CarTable because I can't join based on the CustId I am passed it because it won't exist in the CustomerData table anymore.

I know once solution would be to turn off constraints and delete from CarTable and then delete from CustomerData but just wondering is there a better approach I am not thinking off

So my Query that is throwing the FK error is:

  DELETE car FROM [MySchema].[Car] car 
  JOIN MySchema.CustomerQueryData cqd on car.CarId = cqd.CarId 
  where cqd.CustId = 2

which throws - The DELETE statement conflicted with the REFERENCE constraint

If you are deleting a *customer*, why would you need to do anything to the `Cars` reference table? — Gordon Linoff, Jul 07 '17 at 14:22
Sorry CustId is not the PK on that table - just realised I copied the wrong table - will Edit the question — Ctrl_Alt_Defeat, Jul 07 '17 at 14:29
Can you use [cascading delete](https://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server)? — chrisuae, Jul 07 '17 at 17:09
@chrisuae no unfortunately not..I may have to turn off constraints then delete and then turn on again — Ctrl_Alt_Defeat, Jul 08 '17 at 07:13

delete FK Table Data when passing in the PK of another table

0 Answers0