Delete data from a complexed DB

Question

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 constraints or add triggers or anything like that, I'm planning on removing it in the end.

score 2 · Answer 1 · answered May 25 '11 at 08:35

2

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.

answered May 25 '11 at 08:35

Martin Smith

438,706
87
741
845

1

Agree. Delete is something to be controlled – gbn May 25 '11 at 08:41
1

That didn't worked. I got the error "Introducing FOREIGN KEY constraint fk_... on table MyTable may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION" – Gil Stal May 25 '11 at 08:47
1

Looks like you can't set it up for the whole chain then. – Martin Smith May 25 '11 at 09:01
I agree with @Martin. Instead writing queries , just setting the On Delete Cascade will solve the issue. Besides this, you must know the process to implement it! – Pankaj May 25 '11 at 13:01

score 2 · Answer 2 · answered May 25 '11 at 08:36

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.

score 0 · Answer 3 · edited May 23 '17 at 10:34

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?

Delete data from a complexed DB

3 Answers3