-2

I'm building a small db-cleaner app for a QA sql server database. Naturally, I need to delete table rows with dependencies on them.

T-SQL cascading abilities are very limited, so I've tried using NHibernate to simplify matters. But, the only way I found for this was to create a collection for each dependency in the object-to-delete, and mark that as cascade=delete.

That means creating many, many collections (both in the hbm file and in the C# object) which I don't need for any other purpose. Which makes this method as complicated as just using SQL.

Am I missing something? Is there any easier, more generic way to perform delete-cascade?

Thanks.

EDIT: Just to be clear, I avoid changing the foreign keys in the DB because it's a QA DB, designed to be identical to the production DB.

Tomata
  • 239
  • 1
  • 13
  • 3
    Dependencies are defined *in* your foreign keys. It's quite simple: no enumeration needed, just proper DRI in your database. What more do you need? – gbn Oct 25 '11 at 14:36
  • I'm not sure I understand what you mean. The FKs are not marked as DELETE CASCADE in the DB because it should be identical to the production DB, and the production DB does not support cascading for obvious reasons. – Tomata Oct 25 '11 at 14:41
  • So change the FKs on non-production then. – gbn Oct 25 '11 at 14:50
  • Unfortunately this isn't an option. I'm not allowed to change the structure of the database, only the content. – Tomata Oct 25 '11 at 14:56
  • 1
    If it is a QA DB for final testing, don't you want the same data as production to make sure it all works as expected for the same amount of data? – gbn Oct 25 '11 at 15:01

2 Answers2

0

Eventually I found out a generic way to do the deletion:

This guy wrote a recursive SP which does all the work for you:

http://www.sqlteam.com/article/performing-a-cascade-delete-in-sql-server-7

Needed a little touch-ups (since my DB uses schemas) but works like a charm.

Tomata
  • 239
  • 1
  • 13
0

I suppose you have foreign keys defined between related tables in your database ? You can specify at the foreign key level what should happen with related records when a parent record is being removed.
Check out MSDN for the cascading options, and how to define them:

Cascading FK constraints

Foreign Key Constraints

Frederik Gheysels
  • 56,135
  • 11
  • 101
  • 154