Delete data from dependent tables
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.
3 Solutions collect form web for “Delete data from dependent tables”
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
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
ON CASCADE DELETE from msdn, books, articles and you will find the answer.
Cascading Referential Integrity Constraints