Delete data with foreign key in SQL Server table
Sql ServerSql Server Problem Overview
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.
Sql Server Solutions
Solution 1 - Sql Server
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 - Sql Server
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 - Sql Server
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 - Sql Server
> 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 - Sql Server
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
Solution 6 - Sql Server
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>
Solution 7 - Sql Server
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
Solution 8 - Sql Server
I used triggers to delete entries referencing the primary key. Not sure if you can do that if you can't change the schema, but here is an example in SQLite:
CREATE TRIGGER remove_parent
BEFORE DELETE ON parent
BEGIN
DELETE FROM
child
WHERE
child.id = OLD.id;
END;
This way every time you delete a parent entry, child entries will also be deleted first.
Solution 9 - Sql Server
SET foreign_key_checks = 0; DELETE FROM yourtable; SET foreign_key_checks = 1;
Solution 10 - Sql Server
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.