I'll get into a car on a down hill slope to the Grand Canyon with dodgy breaks.
I should think it would be pretty easy to write procedures to handle child records. This sort of 'automatic override' strategy seemed to be the cause of many of the bugs I found in older Access/JET systems.
First and foremost is data integrity. That is a very specific need to the people that write the checks.
Discussion on:
View:
Show:
And you're right, my reply didn't really acknowledge your comments at the end. It's just that I've run into a lot of [fairly novice] developers that would not do more than scan your last section either, and then write something for members of their work group.
Will I ever forget the 'industrial' application, sold like a product on a store shelf, that would let you delete a major entity, and wipe out a major part of the data store. This was a student registration system. They'd delete instructors and wonder why the students' credentials were gone. They'd delete some students and wonder why it seemed an instructor was under used. This was an old application written off JET 2.0. I was charged with, among other things, writing a backup facility for the thing.
I was a bit over critical on that. I'm sorry for that. Bad netiquette by me. I'll put my hands on the cooker later, will I? *chuckle* I do hope my reply does highlight the cliff edge with widely used applications.
Will I ever forget the 'industrial' application, sold like a product on a store shelf, that would let you delete a major entity, and wipe out a major part of the data store. This was a student registration system. They'd delete instructors and wonder why the students' credentials were gone. They'd delete some students and wonder why it seemed an instructor was under used. This was an old application written off JET 2.0. I was charged with, among other things, writing a backup facility for the thing.
I was a bit over critical on that. I'm sorry for that. Bad netiquette by me. I'll put my hands on the cooker later, will I? *chuckle* I do hope my reply does highlight the cliff edge with widely used applications.
and only scan the middle portion if you must! 
Unfortunately, in order to caution someone about Cascading Actions (or fire arms) one must introduce them to those tools. However, I have occassionally found the NO ACTION option to be quite helpful.
Having the NO ACTION option lets you prevent data from becoming an Orphan unless one consciously makes an enormous effort to do so. In the case of the deletion of the data from the Professors table, would it not have been better to retain at least a portion of the information about the professor (e.g. name, hire date, termination date) so that Classes table could still have a valid reference as well as the StudentCredits and so that at least some sort of minimal validity checking could be made on claims of someone having taken a course under a terminated professor?
If one needs to delete a higher level entity that has children (and grandchildren?), having the NO ACTION option forces the deletion to start at the lowest point and work its way up.
Unfortunately, in order to caution someone about Cascading Actions (or fire arms) one must introduce them to those tools. However, I have occassionally found the NO ACTION option to be quite helpful.
Having the NO ACTION option lets you prevent data from becoming an Orphan unless one consciously makes an enormous effort to do so. In the case of the deletion of the data from the Professors table, would it not have been better to retain at least a portion of the information about the professor (e.g. name, hire date, termination date) so that Classes table could still have a valid reference as well as the StudentCredits and so that at least some sort of minimal validity checking could be made on claims of someone having taken a course under a terminated professor?
If one needs to delete a higher level entity that has children (and grandchildren?), having the NO ACTION option forces the deletion to start at the lowest point and work its way up.
what is equivelenof oracle statement
drop table abc cascade constraints
in sqlserver 2005 or more
drop table abc cascade constraints
in sqlserver 2005 or more
Hi all,
Wait a minute!!! Cascade delete is a powerful feature that can be made available to the user through an "YES" / "NO" warning dialog box. If the user selects YES, the selected entity and all associated child/grandchild entities will be deleted, that is his/her choice. But if NO is selected, the user can delete those specific grandchild and child entities first before attempting to delete the parent entity.
Cascade Delete is like deleting a folder that contains a hierarchy (with sub-folders and files) under it, isn't it ? Windows allows deletion of a given folder and the entire sub-tree. Should not database entities be handled the same way, if the relationships between tables are hierarchical in nature ?
/eeidfn
Wait a minute!!! Cascade delete is a powerful feature that can be made available to the user through an "YES" / "NO" warning dialog box. If the user selects YES, the selected entity and all associated child/grandchild entities will be deleted, that is his/her choice. But if NO is selected, the user can delete those specific grandchild and child entities first before attempting to delete the parent entity.
Cascade Delete is like deleting a folder that contains a hierarchy (with sub-folders and files) under it, isn't it ? Windows allows deletion of a given folder and the entire sub-tree. Should not database entities be handled the same way, if the relationships between tables are hierarchical in nature ?
/eeidfn
The problem we are having with cascade deletes is in the case where a table has foreign keys to two different parent tables, which in turn have a common ancestor. Since SQL Server does not allow both FKs to have a cascade, we put a cascade on only one. When we delete the common ancestor the result is inconsistent. Sometimes SQL is able to do it, and sometimes it tries to delete first from the parent without the cascade, and fails with a reference error.
This seems to be unintelligent behavior on the part of SQL Server. Has anyone found a solution for this? Tim's suggestion to use stored procedures is a lot of trouble, but it's the only foolproof solution I've heard.
This seems to be unintelligent behavior on the part of SQL Server. Has anyone found a solution for this? Tim's suggestion to use stored procedures is a lot of trouble, but it's the only foolproof solution I've heard.
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
