Obedience Training For Databases: Cascading Constraints

Question

Obedience Training For Databases: Cascading Constraints

Phil Factor

SSC-Insane

Points: 20244
More actions
March 29, 2015 at 4:44 am

#398258

Comments posted to this topic are about the item Obedience Training For Databases: Cascading Constraints
Best wishes,
Phil Factor
INTEGER for the data type on Discount
System named primary key indexes and check constraints

Both being minor when it comes to the overall point.

OK, I also choked when reading about cascading updates. The main lesson here is not to drink hot tea while the computer is reading articles to me. I had a bad experience a long tine ago when someone trying to correct a misspelling of a state name deleted the state from the table and added the correct spelling. 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. Now, though, I'm convinced that cascading updates are not such a bad idea.

ATBCharles Kincaid

John Corkett SSCrazy Points: 2273 More actions · Answer 1

Hi,

This was a very good demo.

I have always used a look up table in these situations, but I have used a surrogate key instead of the natural key on the basis that it will use less storage space. However the advantage of using the natural to avoid lots of joins in queries cannot be ignored. I am now thinking the cost of the addiitional storage space is insignificant compared to the extra time and effort required to write queries with additional joins, not to mention the performance benefits of using the natural key.

The other new thing (the point of your article) is using the update cascade. I have always shied away from cascades since I am worried that they might spiral out of control! This however, is a simple use and is far preferable to dropping/disabling a constraint, updating two tables, and adding the constraint back all within a transaction.

Thank you,

John

Phil Factor SSC-Insane Points: 20244 More actions · Answer 2

Yes, it would be good to get some performance figures for really large tables. In the meantime, it is a good idea to be cautious. In the final table, indexes will be required to allow cascading updates to be done without a table scan, because the foreign key isn't, at the moment, indexed. I rather agonized whether to put it in, but though it might distract the reader from the point.

Best wishes,
Phil Factor

Charles Kincaid SSChampion Points: 13593 More actions · Answer 3

I get the one dimension table used in a foreign key constraint and having it in a table rather than "in the code". There is no ALTER CONSTRAINT (IIRC) so it would have to be dropped and recreated for every change. The small table is a brilliant answer. It might be small enough to fit in a single extent.

There were only two things that I did not like

cavebison SSC Rookie Points: 33 More actions · Answer 4

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.

ronmoses@gmail.com SSCarpal Tunnel Points: 4480 More actions · Answer 5

(deleted by author)

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

Charles Kincaid SSChampion Points: 13593 More actions · Answer 6

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

hakim.ali SSCarpal Tunnel Points: 4239 More actions · Answer 7

Good article and good ideas, something to consider in future database designs. Thanks.

Hakim Ali
www.sqlzen.com

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

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);

Phil Factor SSC-Insane Points: 20244 More actions · Answer 9

@mister.magoo

Well spotted. That was sheer force of habit on my part.

Best wishes,
Phil Factor

Phil Factor SSC-Insane Points: 20244 More actions · Answer 10

@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

Thomas J. West-414517 SSC Rookie Points: 31 More actions · Answer 11

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

John Corkett SSCrazy Points: 2273 More actions · Answer 12

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

Phil Factor SSC-Insane Points: 20244 More actions · Answer 13

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

Charles Kincaid SSChampion Points: 13593 More actions · Answer 14

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