Delete data from dependent tables

Question

Is there a query in SQL Server 2008 which will delete data from all dependent tables as well, along with the selected table?

My apologies for not having elaborated on the question. I know that Cascade Delete would work fine, but my application connects to a 3rd party SQL Server db. I have a few Unit Tests which insert into the target table and the dependent tables. Unfortunately the constraints on the target table are not On Delete Cascade and I cannot create them or have them created. I am looking for a generic way of traversing through the dependencies and deleting data in the right order.

If you had integrity constraints (Foreign Keys) with cascade delete rule, you wouldn't have to worry. http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.sqls.doc/sqls292.htm — Alex, Sep 20 '10 at 11:03
Even though the link is for informix documentation the same is true for sql server. — Giorgi, Sep 20 '10 at 11:05
if you have at least foreign keys defined (without cascading deletes), it would be possible to use the system tables to create a dynamic SQL and in effect doing the same as cascading deletes does. If there are no foreign keys defined, there's little hope making a generic solution for it. — Lieven Keersmaekers, Sep 20 '10 at 14:18
@Lieven, Yes there exist foreign key relationships with the dependent tables. Could you please elaborate on your suggestion? — Vijay Ganesh, Sep 21 '10 at 06:31
See http://stackoverflow.com/questions/485581/generate-delete-statement-from-foreign-key-relationships-in-sql-2008 — Ohad Schneider, Sep 24 '12 at 15:34

Lieven Keersmaekers · Accepted Answer · 2013-02-01T09:23:00.977

As there are foreign key relationships, following script could get you started mimicking a cascaded delete.

DECLARE @TableName VARCHAR(32)
DECLARE @PrimaryKey VARCHAR(32)

SET @TableName = 'MasterTable'
SET @PrimaryKey = '1'

SELECT  'DELETE FROM '
        + fks.name + '.' + fkt.name
        + ' WHERE '
        + pc.name 
        + ' = '
        + @PrimaryKey
        , fko.name as [FK Name]
        , fk.constraint_column_id as [Col Order]
        , fks.name + '.' + fkt.name as [FK table]
        , pc.name as [FK column]
        , rcs.name + '.' + rct.name as [PK table]
        , rc.name as [PK column]
FROM    sys.foreign_key_columns fk
        -- FK columns
        INNER JOIN sys.columns pc ON fk.parent_object_id = pc.object_id
                                     AND fk.parent_column_id = pc.column_id
        INNER JOIN sys.objects fkt ON pc.object_id = fkt.object_id
        INNER JOIN sys.schemas as fks ON fks.schema_id = fkt.schema_id
        -- referenced PK columns
        INNER JOIN sys.columns rc ON fk.referenced_object_id = rc.object_id
                                     AND fk.referenced_column_id = rc.column_id
        INNER JOIN sys.objects rct ON rc.object_id = rct.object_id
        INNER JOIN sys.schemas as rcs ON rcs.schema_id = rct.schema_id
        -- foreign key constraint name
        INNER JOIN sys.objects fko ON fk.constraint_object_id = fko.object_id
WHERE   rct.Name = @TableName

score 2 · Answer 2 · answered Sep 20 '10 at 11:10

To do that you set up a constraint between the tables with cascading delete. You can do that in a diagram by dragging a connection between fields and editing the properties, or using a query:

 alter table SomeTable
 add constraint SomeConstraint
 foreign key (SomeField) references SomeOtherTable (SomeOtherField) 
 on delete cascade

score 1 · Answer 3 · answered Sep 20 '10 at 11:09

1

Read about ON CASCADE DELETE from msdn, books, articles and you will find the answer.

Cascading Referential Integrity Constraints

answered Sep 20 '10 at 11:09

Kashif

14,071
18
66
98

Delete data from dependent tables

3 Answers3

Linked