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.

score 62 · Answer 1 · answered Nov 24 '11 at 01:17

62

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

answered Nov 24 '11 at 01:17

Chris Fulstow

41,170
10
86
110

10

If you disable then re-enable the constraints, won't the re-enable fail due to the broken `foreign key` references? – Adam Wenger Nov 24 '11 at 01:54
2

No, it only checks the constraint when you write new values to a field - it doesn't rescan the whole table when you put the check back in. – Alastair Maw Nov 24 '11 at 02:04
3

Well. This means that all the data in the child table will remain there. I'm about to delete 2k rows, and I think it will be bulky if I keep the data in the child table. I think I'll just do it manually. Thx anyway for the replies guys. You deserve +1 :) – Andha Nov 24 '11 at 02:20
2

this seems a little "kludge-y" but in my particular case, I'm glad to have found this and I'm using it. – Hewins Jun 27 '13 at 16:12

score 52 · Answer 2 · answered Nov 24 '11 at 01:52

52

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

answered Nov 24 '11 at 01:52

Adam Wenger

17,100
6
52
63

1

Can these two DELETEs be combined in a way that I only have to write the part once? – Thomas Tempelmann Jul 25 '17 at 08:35
1

I don't believe so @ThomasTempelmann. If you want to only write the condition once, you'd have to turn on cascade deletes instead of manually dealing with each table. Alastair Maw's answer here references this option as well. – Adam Wenger Aug 04 '17 at 13:05

Alastair Maw · Answer 3 · 2011-11-24T01:30:29.667

24

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.

edited Nov 24 '11 at 01:30

answered Nov 24 '11 at 01:15

Alastair Maw

5,373
1
38
50

ah. I missed to tell that part in my post, I have to get it worked without changing the schema. it's possible right ? – Andha Nov 24 '11 at 01:22
1

No, it's not possible with some magic query OPTION or whatever. You need to manually do the deletes. – Alastair Maw Nov 24 '11 at 01:26

Sam Arul Raj T · Answer 4 · 2014-08-28T09:32:56.517

10

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

edited Aug 28 '14 at 09:32

answered Feb 13 '13 at 10:01

Sam Arul Raj T

1,752
17
23

Still not working. Here is my error `ERROR: update or delete on table "question" violates foreign key constraint "answer_question_id_fkey" on table "answer" DETAIL: Key (question_id)=(14) is still referenced from table "answer". SQL state: 23503` – Prathamesh More Feb 13 '20 at 06:53

score 3 · Answer 5 · edited May 09 '12 at 00:24

3

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>

edited May 09 '12 at 00:24

Taryn

242,637
56
362
405

answered May 08 '12 at 08:30

Azeem Raza Tayyab

63
1
6

This is the best answer if you're not cascading delete. Cursors perform horribly and removing the constraints to delete could leave orphaned rows in other tables. – Corv1nus Mar 01 '18 at 13:48

score 3 · Answer 6 · answered Nov 24 '11 at 01:44

3

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

answered Nov 24 '11 at 01:44

Igor Borisenko

3,806
3
34
49

score 1 · Answer 7 · answered Feb 02 '17 at 09:26

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

score 0 · Answer 8 · answered Jun 06 '21 at 19:21

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.

score 0 · Answer 9 · answered Mar 10 '23 at 18:10

0

Simple solution:

Delete an sub-table Using MySQL control panel:

answered Mar 10 '23 at 18:10

C.F.G

817
8
16

Tagged sql-server, not MySql – thetechnician94 Jul 19 '23 at 19:10

Shaunlgs · Answer 10 · 2019-08-19T13:26:09.257

-1

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.

edited Aug 19 '19 at 13:26

answered Nov 23 '18 at 15:40

Shaunlgs

59
5

3

The question is about SQL Server, not MySQL – Graham Laight Aug 14 '19 at 14:45
This answer causes datainconsistency within you database. Use it only if you know what you're doing. It would be nice, if @Shaunlgs would've explained more. – DarkTrick Aug 18 '21 at 22:38

score -1 · Answer 11 · edited Sep 29 '22 at 17:46

-1

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

edited Sep 29 '22 at 17:46

Audwin Oyong

2,247
3
15
32

answered Jun 27 '19 at 08:47

Amir Mofakhar

6,843
2
14
5

2

This answer causes datainconsistency within you database. Use it only if you know what you're doing. – DarkTrick Aug 18 '21 at 22:38
Tagged sql-server, not MySQL – thetechnician94 Jul 19 '23 at 19:16

Delete data with foreign key in SQL Server table

11 Answers11

Simple solution:

Linked