SQL Delete Query Foreign Key constraint

Question

I am trying to Delete record with primary key as foreign key in two tables. Everytime I try to run the query it gives me this error:

Query:

DELETE FROM BusinessPlus_Post
FROM BusinessPlus_Post 
INNER JOIN BusinessPlus_PostImage ON BusinessPlus_Post.Post_ID = BusinessPlus_PostImage.BusinessPlusPost_ID 
INNER JOIN BusinessPlus_PostTag ON BusinessPlus_Post.Post_ID = BusinessPlus_PostTag.BusinessPlusPost_ID
WHERE 
    (BusinessPlus_Post.Post_ID = 3) 
    AND (BusinessPlus_PostImage.BusinessPlusPost_ID = 3) 
    AND (BusinessPlus_PostTag.BusinessPlusPost_ID = 3)

Error:

The DELETE statement conflicted with the REFERENCE constraint "FK_BusinessPlusPostImage". The conflict Occurred in database "BusinessPlus_AdminPanel_Database", table "dbo.BusinessPlus_PostImage", column 'BusinessPlusPost_ID'. The statement was terminated.

Answer 1

Right now, you are only stating that you want to delete the BusinessPlus_Post record, but not the BusinessPlus_PostImage and BusinessPlus_PostTag records. This would lead to problems, as we then would have 'orphan' PostImage and PostTag records with no corresponding Post records.

Apparently, it is not possible to delete from multiple tables in SQL Server (it is supported in MySQL, for example).

You have to split your queries, and delete from the 'child' tables first:

DELETE FROM BusinessPlus_PostImage
  WHERE BusinessPlusPost_ID = 3

DELETE FROM BusinessPlus_PostTag
  WHERE BusinessPlusPost_ID = 3

DELETE FROM BusinessPlus_Post
  WHERE Post_ID = 3