Deleting from Parent and all its child tables with FK (no DELETE ON CASCADE)

Question

Hi Experts,

I have a database with many tables. I would like to Delete all rows with practiceID=55 from all Parents tables and all corresponding rows from its child tables. Tables are linked with foreign key constraints (but there is no ON DELETE CASCADE). 
Can any one tell how to write a generalized code for removing rows from both parent and child tables. 

Query should pick parent table one by one and delete rows with practiceID=55 and all corresponding rows from its child tables

Thanks,
Naveen J V

Answer 1

Hi Naveen.

Just to understand it better:

Delete all doctors allocated to practiceID 55,

All patients allocated to doctors that are allocated to practice55?

All records related to patients allocated to doctors that area allocated to practice 55 ?

It can destroy your but is your call.

Or delete only records from tables that have a column PracticeId ?

---

karepa

Answer 2

If you don't have ON DELETE CASCADE option set with FK constraint, then you can create a DELETE TRIGGER and inside this trigger delete all corresponding records from the child table.

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011 | My FB Page

Answer 3

You can also do it inline in code using OUTPUT clause

http://blogs.msdn.com/b/sqltips/archive/2005/06/13/output-clause.aspx

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 4

Hi Karepa,

When we delete rows with practiceid=55 from the parent table all the transaction related these rows should be get deleted from the child tables. There is no ON DELETE CASCADE constraint on the table now.

Thanks,

Naveen J V

Answer 5

Hi Manoj,

We cannot create trigger in this point in time. Table and data already exists and I would like to cleanse the data as per the criteria mentioned.

I am asked to derive a query for cleaning the data where PracticeId=55 from all parent tables and corresponding transactions from its child.

Thanks,

Naveen

Answer 6

So, this is a one time cleanup, should be simple.

First, delete from Child table, then from the Parent table, with filter on column PracticeId=55.

Even if it reoccurring, put these 2 stmts in an SP and schedule it for every day, once a week/months.

Why do you think its complex? Can you please explain more?

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011 | My FB Page

Answer 7

Manoj,

I have been given a database of more than 1500+ tables. We cannot manually identify the tables.

Script should be able to delete from all those tables in one shot. It should identify the parent table and its child with in a query and should work for all the tables.

Thanks,

Naveen J V

Answer 8

Then I this case you need to create query by using system catalog tables/views, link: http://sqlwithmanoj.com/2010/12/06/querying-sql-server-metadata/

Query:

DECLARE @Deletestr VARCHAR(max) = ''
SELECT @Deletestr = @Deletestr +
'DELETE FROM ' + object_name(fk.referenced_object_id) + 'WHERE ParentId = 55
GO
'
from sys.foreign_keys fk
join sys.foreign_key_columns fc
on fk.object_id = fc.constraint_object_id
where object_name(fk.parent_object_id) = 'SalesOrderHeader'

PRINT (@Deletestr)

EXEC (@Deletestr)

Like above query get the linked tables (FK) info, and finally with the output create Dynamic DELETE statement.

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011 | My FB Page