Cascading delete was on everywhere. So when the bad state went away then so did the customers, and their orders, and their payments, and ... A very bad day indeed.
Oh dear! This is why I've never implemented cascading deletes, ever. Being a programmer, I know too well how users can do things you don't expect, and so I want to be able to prevent / double-check their actions in code.
In fact I don't even like cascading updates, because you can connect to a database in lots of ways, not just through the application you *assume* people will use to update the database. A new IT contractor, or anyone who (perhaps accidentally) has the db login, can inadvertently, or on purpose, delete something that sets off cascade operations - no thanks!
Call me paranoid, but I prefer my constraints to *constrain*, not enable. 
If someone wants to delete a contact record, they better be prepared to spend a few hours *manually* deleting all those transactions, invoices, etc. first. Seriously, cascaded anything is questionable - why on Earth would we make it easier for someone to mess with a db?
As for the cascading update shown in the article... In most cases, those top-level lookup tables are set up like that for a *reason* - usually tied to real-world business rules and usually are not going to change very often. So why add a mechanism allowing them to change easily on a whim? The lack of cascading updates *protects* the data in those lookup tables. If one needs to change, it's done through the application layer, which can implement any number of checks to ensure it's being done by the right person, at the right time, for the right reasons. High-impact changes, whether updates or deletes, shouldn't be made easy to do.
(deleted by author)
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
I get what you are saying.
Call me paranoid, but I prefer my constraints to *constrain*, not enable.
The name of a thing should match what it does and a thing should be true to its name.
I found that that largely comes from having nomenclature drilled in to me. Then I got into contracts and got hung on the horns of the difference between a constraint and a restriction. Driving down the road I am restricted to the posted speed limit as a maximum but may drive slower. If I accept payment for a service then I am constrained to perform that service.
The referential integrity is the "contract", the agreement to agree. The constraint, at least in terms of the foreign key, is the "teeth" that enforces the contract.
ATBCharles Kincaid
Good article and good ideas, something to consider in future database designs. Thanks.
Hakim Ali
www.sqlzen.com
Thanks for an interesting read, sometimes it's worth revisiting those ideas that you have put aside just in case you see a new angle on them.
I just wish you hadn't included a default colour 'Black', as that invalidates the effectiveness of the foreign key with cascaded updates - what if someone updates Black to Jet, then the default on the column becomes invalid and a "code change" is required.
Otherwise, a good read, thanks.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);@mister.magoo
Well spotted. That was sheer force of habit on my part.
Best wishes,
Phil Factor
@lectroid10-sqlservercentral
I certainly agree that cascading constraints can be dangerous, and I agree that this is an enabler rather than a constraint so it doesn't really do 'what it says on the tin'. I've never used cascading deletes- they strike me with terror. Certainly, the working of every cascaded update should be checked in detail before you use them in a production system. (see, for example, Paul White's brilliant answer here Performance tuning a cascading delete with many foreign keys ) However, what I like about the technique is that the data tends to be more 'visible' and so it is easier to trawl for errors. Also, one can create a well-normalized database without looking over your shoulder the whole time and worrying about performance issues.
Best wishes,
Phil Factor
Phil
I enjoy your articles.
This was a good one.
I especially liked to reference to the loyal opposition to natural normalization.
WHat did you call them I cant forget but it raised a fight-or-flight response in me so you must have struck a nerve! 
Truth hurts I suppose
O yea goons Lol! that's me the version control Nazi and code fascist
O I wouldn't argue with you if I were on a project you were leading. Well not much I suppose.
That wouldn't be because I agreed with you it would be because I do not like to engage in wars I cant win.
Kind of religious territory here
I try to be tolerant and I can see the advantages of the approach you so very aptly illustrate.
I know you know on size doesn't fit all.
I respect your opinion and skill.
here it comes ...
I am almost always opposed to using natural keys because management can change them.
Thanks for listening and for all you do
Thank you for being the best you can
I will try to do the same
Your Friend Tom
Thomas J. West-I am almost always opposed to using natural keys because management can change them.
Hi Tom,
I didn't see this as part of the natural/surrogate key debate. Is saw this more as avoiding having the valid values for a column removed from one or more check constraints and placed in a small lookup table. There is no need for a surrogate key in such a situation (except for saving storage space) and the beauty is that if management do change their mind about the valid column values then you only have to amend the values in the look up table and do not have drop and recreate the check constraint(s).
As you observe one size doesn't fit all either in the natural vs surrogate debate, nor in this update cascade matter. It is really up to the developer to decide what is most appropriate in their unique situation. Phil was just making us aware of another tool that we may have not considered before.
Kind regards,
John
Surrogate keys have their place, but by using natural keys where appropriate, you talk the domain language, in much the same way as you do by naming your database objects with care. It is another good way of making sure that the team that works on the database have a shared understanding of the business domain. I find that databases that do this are easier to work on and maintain. When I was a young programmer, 5Mb disks were huge things the size of a large shoebox(I have one in my attic as a souvenir), so you couldn't then afford the luxury of natural keys. Even now, large tables need keys that are narrow as a whisker if they are to perform well. That means surrogates, incrementing integers. However, that doesn't mean that you need to use them everywhere. It is a matter of judgement.
However, Check Constraints are always important, and I cringe to see tables without them, just because I've witnessed, and sometimes experienced, many disasters caused by bad data getting into tables. Almost always, the developers have blinked in bewilderment and muttered 'but that's impossible', when it happens.
Best wishes,
Phil Factor
Phil Factor (4/2/2015)
However, Check Constraints are always important, and I cringe to see tables without them, just because I've witnessed, and sometimes experienced, many disasters caused by bad data getting into tables. Almost always, the developers have blinked in bewilderment and muttered 'but that's impossible', when it happens.
I got excited about using a tiny table and a foreign key in place of an enumerated check constraint. The "cost" of the additional (not extra) table is minimal.
I too had been faithful to the mantra that there was very little that was natural about natural keys. Four byte pointers made for damn fast look ups. So two byte pointers with no surrogate overhead got me drooling. (OK so there is the overhead of Nvarchar over Nchar.)
ATBCharles Kincaid
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply