Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shapper
Constraint Violating Yak Guru
450 Posts |
 Posted - 2010-07-13 : 08:48:10
|
| Hello,When should I cascade on update or on delete?On delete I am cascading all relationships not including the ones with Lookup tables.But should I use it on Update?Thanks,Miguel |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-13 : 09:13:32
|
I don't use CASCADE on either.For DELETE our Sproc pre-deletes any "Child" records where the business rules allow. For example, deleting a Product record may allow deletion of the associated Stock Record [perhaps only if Stock level = 0] and any records about how often the product was viewed [i.e. statistical data only] etc. But the business rules do not allow deleting an Order Item that references that Product.So my Stored Procedure would Begin a Transaction, delete the Stock and ProductViews records, then the Product record - which would fail if Order Items existed.The User would then have to manually delete the order items (if that was a sensible thing to do! e.g. only Test Orders exist) in order to THEN be able to delete the Product Record.I wouldn't want to let Cascade Delete just do that "out of sight our of mind"!Trying to update related Foreign Key records is tricky, so Cascade can be useful on Update, but quite a few of our Foreign Keys are sufficiently complex that SQL doesn't allow CASCADE UPDATE anyway  |
 |
|
|
Devart
Posting Yak Master
102 Posts |
Posted - 2010-07-13 : 09:17:54
|
| Kristen is true. I don't use cascade update/delete too.Devart Team |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-07-13 : 16:27:08
|
| NEVER NEVER NEVER use cascade |
|
|
|
OCD Dan
Starting Member
5 Posts |
Posted - 2011-07-31 : 16:22:38
|
| why never use cascade, can someone explain please? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-31 : 16:40:35
|
| Unexpected deletes, inexplicable missing data. I had someone once who had a nice database design, hierarchy of tables, everything eventually referred to one of two rows in a master table. He accidentally deleted of of those master rows. 3/4 of the database gone.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
On Update Cascade. Should I use it?