Delete data with foreign key in SQL Server table

Solution 1:

You can disable and re-enable the foreign key constraints before and after deleting:

alter table MyOtherTable nocheck constraint all
delete from MyTable
alter table MyOtherTable check constraint all

Solution 2:

You need to manually delete the children. the <condition> is the same for both queries.

DELETE FROM child
FROM cTable AS child
INNER JOIN table AS parent ON child.ParentId = parent.ParentId
WHERE <condition>;

DELETE FROM parent
FROM table AS parent
WHERE <condition>;

Solution 3:

If you wish the delete to be automatic, you need to change your schema so that the foreign key constraint is ON DELETE CASCADE.

For more information, see the MSDN page on Cascading Referential Integrity Constraints.

ETA (after clarification from the poster): If you can't update the schema, you have to manually DELETE the affected child records first.

Solution 4:

here you are adding the foreign key for your "Child" table

ALTER TABLE child
ADD FOREIGN KEY (P_Id)
REFERENCES parent(P_Id) 
ON DELETE CASCADE
ON UPDATE CASCADE;

After that if you make a DELETE query on "Parent" table like this

DELETE FROM parent WHERE .....

since the child has a reference to parent with DELETE CASCADE, the "Child" rows also will be deleted! along with the "parent".

Solution 5:

So, you need to DELETE related rows from conflicted tables or more logical to UPDATE their FOREIGN KEY column to reference other PRIMARY KEY's from the parent table.

Also, you may want to read this article Don’t Delete – Just Don’t