Delete data with foreign key in SQL Server table

Question

I'm going to delete data in an SQL Server table (parent) which has a relationship with another table (child).
I tried the basic Delete query. But it isn't working (and I know it won't).

DELETE FROM table WHERE ...

It returned following error

The DELETE statement conflicted with the REFERENCE constraint ...

I need to keep the table's schema. I know that I just need to add some words in the query, I've ever done this before, but I just couldn't recall it.

Answer 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

Answer 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>;

Answer 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.