[sql-server] Delete data with foreign key in SQL Server table

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.

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.

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

To delete data from the tables having relationship of parent_child, First you have to delete the data from the child table by mentioning join then simply delete the data from the parent table, example is given below:

DELETE ChildTable
FROM ChildTable inner join ChildTable on PParentTable.ID=ChildTable.ParentTableID
WHERE <WHERE CONDITION> 


DELETE  ParentTable
WHERE <WHERE CONDITION>

SET foreign_key_checks = 0; DELETE FROM yourtable; SET foreign_key_checks = 1;

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

Set the FOREIGN_KEY_CHECKS before and after your delete SQL statements.

SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM table WHERE ...
DELETE FROM table WHERE ...
DELETE FROM table WHERE ...
SET FOREIGN_KEY_CHECKS = 1;

Source: https://alvinalexander.com/blog/post/mysql/drop-mysql-tables-in-any-order-foreign-keys.

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

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

Usefull script which you can delete all data in all tables of a database , replace tt with you databse name :

declare @tablename nvarchar(100)
declare c1 cursor for
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG='tt' AND TABLE_TYPE='BASE TABLE'

open  c1
fetch next from c1 into @tablename

while @@FETCH_STATUS = 0
    begin
    print @t1
        exec('alter table ' + @tablename + ' nocheck constraint all')
        exec('delete from ' + @tablename)
        exec ('alter table ' + @tablename + ' check constraint all')
        fetch next from c1 into @tablename
    end
close c1
DEALLOCATE c1