How to delete a "tree" of data from sql?

Question

I have a table structure similar to:

Portfolios
  Properties
   Units
    Leases

All tables are setup with foreign key relationships and cascade deletes. I want to be able to delete a portfolio, which would in turn delete all properties assigned to that portfolio, all units assigned to those properties, and all leases assigned to those units.

I'm getting errors similar to:

The DELETE statement conflicted with the REFERENCE constraint "FK_Leases_Units". The conflict occurred in database "MyDb", table "Leases", column 'UnitId'.

What is the proper way to delete a "tree" of data like this?

If it matters, I'm using MS SQL Server 2008.

Answer 1

Are you sure that FK_Leases_Units is setup to cascade delete? Every FK on the tree must be setup to cascade delete or none of it works...looks like the lowest level isn't set this way.

I'd double check this, if it's setup to cascade, you shouldn't be getting that error...it'd delete all rows with that UnitId instead of complaining the parent's missing.

Answer 2

You have to make sure you delete the records in the right order. you can't delete a record that is a key to another record in another table. Check you deletion order and you'll find the problem.