May 5, 2018 at 6:16 am
There is a healthy debate going on between a couple teams in my area regarding the use of the cascade delete option of foreign keys. I think like most features they can be abused and misused however I don’t land on banishing their use completely like some others in the debate. I’ve heard a lot of arguments for and against their use just wondering, what’s your point of view?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 5, 2018 at 11:44 am
My take on it is probably similar to yours. There are very few "NEVERs" in SQL Server.
I'd say that (as normal) "It Depends". For Cascade Deletes, it depends on the fan out of deletes as they will all occur in a single transaction and can cause some extended blocking if the number of tables involved and the numbers of rows to be deleted are large. If the number of tables is large, the cascading delete could be blocked for a substantial period of time depending on what else is going on in the server.
It IS, very handy because, hopefully, any new tables or changes to the tables will also be caught by the presence of the correct FKs and so the deletes wouldn't require the maintenance of a special proc to do the deletes in the correct order.
Of course, you already know that but thought I'd mention it for other readers that may not know it.
The bottom line is that "It Depends" and someone needs to do an analysis as to the fan out, how it would be used, and at least an think about what the unintended consequences are/could be especially since the Cascading Deletes are a form of "hidden" functionality much like triggers and a couple of other things. For example, if someone unintentionally deletes from the root level, instead of being prevented, the deletes would auto-magically ripple through the tree instead of preventing the unintentional delete. Or, perhaps, someone decided it would be a cool way to delete legacy rows from years back and ended up locking up all of the tables for an extended period of time instead of doing something a bit more unobtrusive.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2018 at 9:10 pm
Orlando Colamatteo - Saturday, May 5, 2018 6:16 AMThere is a healthy debate going on between a couple teams in my area regarding the use of the cascade delete option of foreign keys. I think like most features they can be abused and misused however I don’t land on banishing their use completely like some others in the debate. I’ve heard a lot of arguments for and against their use just wondering, what’s your point of view?
My take is that cascading deletes are an integral part of DRI...Barring specific odd-ball cases where locking/blocking or deadlocks, my vote would be that cascading both deletes and updates should be the rule and not using them should be the exception.
That said, I'm open to changing my mind if presented with compelling evidence.
May 6, 2018 at 3:24 am
I can not pass judgement either way, just to say I never use CASCADE deletes.
This probably stems from past experience of a root delete cascading to an empty database (luckily I had good backups and recovery 🙂 ) or maybe my databases are not sophisticated enough or warrant such.
In cases where I have to do hierarchical deletes I code procedures appropriately. As Jeff pointed out it involves more work if the structure changes but I consider that minor inconvenience in respect of data integrity.
Far away is close at hand in the images of elsewhere.
Anon.
May 7, 2018 at 7:36 am
Like any other tool, cascade delete is at least as dangerous as it is powerful. If you're going to make use of that kind of power, then you have to have some serious think time on how, exactly; things will work with that feature enabled. It's the kind of power that imposes significant responsibility. You have a LOT to lose if you don't do it right. I'm not in favor of using it UNTIL there's a demonstrated and tested methodology that keeps risk of data loss at a reasonable level for the organization's capacity to repair a situation gone bad. If your recovery processes are well-tested, mature, and repeatable, and your DBA personnel have the demonstrated capacity to fix/resolve issues and are highly experienced at recovery and problem solving, then with well-designed and properly planned and tested methodology, there's no reason to put a total ban in place. I'd just look to have a fair number of controls in place to prevent indiscriminate use of it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 7, 2018 at 8:00 am
Orlando Colamatteo - Saturday, May 5, 2018 6:16 AMThere is a healthy debate going on between a couple teams in my area regarding the use of the cascade delete option of foreign keys. I think like most features they can be abused and misused however I don’t land on banishing their use completely like some others in the debate. I’ve heard a lot of arguments for and against their use just wondering, what’s your point of view?
The reason we put Declarative Referential Integrity (DRI) actions into the SQL standards was that people used to do it by hand. One of our committee members looked at the actual use of triggers and found that most of them were doing cascade deletes and updates in production databases.
But the real problem was they were not always doing it when they should, so lack of DRI actions was creating bad data. The full ANSI/ISO standards are a bit more complicated than what SQL Server has, but we do have does 80 – 90% of the work in the real world (simple cascades and default values).
The other nice part about DRI actions is a relatively cheap compared to doing this in the application layer, or in other procedural code. Try to put as much work on your database engine as you possibly can. It gives you one point of maintenance instead of 1000 application programs. It is also probably faster. And it isn't faster now, don't worry. The next release of your database engine will have some optimizations on it.
Of course this means that you have to have a good data model and actually designed your schema before you started coding. Yes, you can make a mistake and cascade things that you didn't want to, but is the Romans say "not even the gods themselves can prevail against invincible stupidity"
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply