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

  • 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
    Wednesday, April 8, 2015 8:00 AM

Answers

All replies

  • 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

    Wednesday, April 8, 2015 8:08 AM
  • 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

    Wednesday, April 8, 2015 9:05 AM
  • 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

    Wednesday, April 8, 2015 9:16 AM
  • 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

    Wednesday, April 8, 2015 9:52 AM
  • 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

    Wednesday, April 8, 2015 10:03 AM
  • 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

    Wednesday, April 8, 2015 10:07 AM
  • 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

    Wednesday, April 8, 2015 10:14 AM
  • 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

    Wednesday, April 8, 2015 10:49 AM