Delete data from a complexed DB

Let's say I have a Customers table with columns ID and CompanyId.

I need to delete some companies from the DB.

The problem is, the table has a lot of child tables and those tables also has a lot of child tables and so on...

Just to clarify, all the relationships are with constraints.

How can I accomplish that ?

Thanks.

EDIT: Notice that what i'm trying to do is a one time operation.

Whether i will change the const开发者_运维问答raints or add triggers or anything like that, I'm planning on removing it in the end.

The inbuilt solution to this problem is to set up your FK constraints with ON DELETE CASCADE.

However many people (myself included) are somewhat uneasy about doing this as a mistaken delete will silently propagate through the database.

Here are three ways:

• Use a stored procedure to delete child first then up to the parent row in a transation.

I personally wouldn't make it dynamic and would have a specific "DeleteCompany" proc. Your may need a rule that such as "no delete if sales > 100 million" that needs checked

• CASCADE DELETEs on your foreign keys

This can be tricky if you have multiple cascade paths, but simple otherwise

• INSTEAD OF trigger

An INSTEAD OF trigger is like a stored procedure in operation. Note: You'll get an FK violation before an AFTER trigger fires

Personally, I'd use a stored proc so I have explicit deletes. The effect is the same as cascading FKs but more obvious.

For SQL Server 2008, this is the solution:

Generate Delete Statement From Foreign Key Relationships in SQL 2008?

With this solution, you can easily find the correct sequence of DELETE respecting, in the meantime, the foreign keys' relationships.

If you are interested in this theme, you can read also the ORACLE PL/SQL solution:

How to generate DELETE statements in PL/SQL, based on the tables FK relations?