How can I delete all dependent rows of a table?

Question

I have a table A which has primary key column a and it is a foreign key to B table (Table B has primary key b and column a). Table B also has a primary key b which foreign key to C table and so on.

It won't allow me to delete a row from table A because its dependent on table B and table B is dependent on table C. So I have to delete a row from C first then B and at last A manually.

Is there any way to delete directly without going to each table and checking if it has any dependencies before deleting?

Answer 1

If you're not allowed to modify the tables, you can take a look at the EXISTS operator.
It will allow you to delete rows from a table only if the query inside the EXISTS return at least 1 result. You can use it to check dependencies.

You can write 3 queries:

DELETE C c
WHERE EXISTS (SELECT 1
              FROM B b
              WHERE c.b = b.b
              AND EXISTS (SELECT 1
                          FROM A a
                          WHERE a.a = b.a
                          AND ... ));
DELETE B b
WHERE EXISTS (SELECT 1
              FROM A a
              WHERE a.a = b.a
              AND ...);

DELETE A a
WHERE ...

The first one will take care of record in C that references records in B that references records in A that you want to delete.
Then you can remove records from B since there is no more dependencies in C.
Finally, you're able to delete records from A using the same logic.

Answer 2

You Can Use Cascade Cascading Referential Integrity Constraints

Update: you should enable Cascading Referential Integrity Constraints of Table A (PK) from Table B where ID of A is foreign Key and similarly of PK of Table B From Table C Where ID of B is foreign Key

MSDN LIBRARY

CODE PROJECT ARTICALE

Very Nice Article BLOG.SQL AUTHORITY

Answer 3

The term you're looking for is 'cascade' - you need cascading deletes. You can enable them, as explained here: How do I use cascade delete with SQL Server?

Answer 4

You could use cascading referential integrity, see this question. However, beware, too much cascading RI and you can do a lot of damage without realising it!

Answer 5

As others have already pointed out, cascade delete is what you are looking for. In one of the comments, you have mentioned that you do not know the dependencies. Here is a script that will list the dependencies

SELECT Db_name()                                   referencing_database_name, 
       Object_name (referencing_id)                referencing_entity_name, 
       Isnull(referenced_schema_name, 'dbo')       referenced_schema_name, 
       referenced_entity_name, 
       ao.type_desc                                referenced_entity_type, 
       Isnull(referenced_database_name, Db_name()) referenced_database_name 
FROM   sys.sql_expression_dependencies sed 
       JOIN sys.all_objects ao 
         ON sed.referenced_entity_name = ao.name  
WHERE ao.type_desc = 'USER_TABLE' 

Answer 6

The automatic deletions depend on how you defined the relationships between the tables.

If you get an error when you try to delete a row in a master table, that's because you didn't specify an special action in the relationships for deletion.

What I mean is changing the ON DELETE and ON UPDATE options in the FK. This allows to specify what happens with the child rows when you delete or update the row in the main table. For example, the ON DELETE CASCADE option would do exactly what you want.

For example:

CREATE TABLE Sales.TempSalesReason (
  TempID int NOT NULL, Name nvarchar(50
), 
CONSTRAINT PK_TempSales PRIMARY KEY NONCLUSTERED (TempID), 
CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID) 
  REFERENCES Sales.SalesReason (SalesReasonID) 
  ON DELETE CASCADE
  ON UPDATE CASCADE
)

would make that, when you delete or modify a row in the main table, the row is also modified or deleted on the child table.

Relevant docs:

• Create Foreign Key Relationships

• Cascading Referential Integrity Constraints

Answer 7

There is a stored procedure 'sp_fkeys' which will give all foreign keys for a specific table

exec sp_fkeys 'My_Table'

So you could save the output to temp table and then in cursor you will delete all rows that have any dependency. If any nested table have dependencies too you will do the same but for the nested table (recursion)