TSQL, delete row and all foreign key related rows

Question

I am wondering if there is a query for deleting a row which also deletes all related foreign key related data.

I have a database with a table which refers through a foreign key to other rows in the same table

ID  |  ParentID  |  Data fields
1   |  NULL      |  text and more data
2   |  1         |  Text and more data
3   |  2         |  ""

As you see item 2 refers to 1 using a foreign key. Currently I am unable to delete object 1 because object 2 and 3 still exist in the database. I want to know if there is a way/query (without changing the table settings) to delete object 1 and therefore also deleting 2 and 3.

DELETE FROM Table WHERE ID = 1 -- And this must then also delete row 2.
DELETE FROM Table WHERE ID = 1 OR ParentID = 1 -- Obviously doesn't work either.

I am aware that it is possible to turn of foreign key constrainst for a table, but I want to know if it can be done with a query without changing these settings.

@AshimDas: `on delete cascade` is an option for `add constraint ... foreign key`, not for the `delete` statement — Andomar, Jul 16 '15 at 10:13

score 0 · Accepted Answer · edited May 23 '17 at 12:14

0

Best way would be enabling CASCADE DELETE. Read more here: How do I use cascade delete with SQL Server?

edited May 23 '17 at 12:14

Community

1
1

answered Jul 16 '15 at 09:55

Evaldas Buinauskas

13,739
11
55
107

TSQL, delete row and all foreign key related rows

1 Answers1