John W. Colby
jcolby at colbyconsulting.com
Sun Feb 2 05:40:00 CST 2003
John, You are correct but there is another way of looking at this. The LACK of cascade delete PREVENTS people that aren't supposed to be deleting records from deleting them. So by turning it OFF you are ENFORCING that no one except those that are supposed to delete them is allowed to (by running delete queries). By turning it ON, you are allowing anyone and their grandmother to delete records that they aren't supposed to. What a nightmare! Again, it boils down to "is EVERYONE supposed to be able to delete these records, or just a few"? If everyone is supposed to be able to delete things then there's no point in turning it off. In my databases I absolutely do NOT want the operators deleting things. Delete a type of claim status and sure it warns you "related records will be deleted" but if they don't understand that, then claims get deleted, but wait... claims can't be deleted without claim contacts, payments, ATPs, expenses, etc. So some person who has no business deleting the thing in the first place has just ripped out god knows how many records in a dozen different tables. I am absolutely a fan of referential integrity. Turn it on by all means - use it as it was meant to be used. Once you have turned it on however, what is the point of referential integrity if the salesman down the hall has the ability to construct a query and "oops" I hit delete... can you get my data back?". You just made damned sure that he deleted the world by enforcing referential integrity but leaving cascade delete on. Don't take this the wrong way, but the correct way to handle what you need to do is to analyze what tables need records deleted, in what order they need to be deleted to allow RI to be enforced, build delete queries to do the deletes, execute them in the correct order, and wrap them in transactions so that if any fail they all fail. You are using cascade delete as the lazy man's way out. And in the process you are exposing the db to anyone who should NOT be allowed to do the deletes, as well as those who should. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-admin at databaseadvisors.com [mailto:accessd-admin at databaseadvisors.com]On Behalf Of John Bartow Sent: Saturday, February 01, 2003 9:37 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Cascade-delete (was: Estimating Help) I've always seen this as the difference of where you come from, ie. db administrator or programmer. A db admin would want it in the db so that no programmer can miss the logic and screw things up - a programmer would want to do it in code. I came from the DB admin. of Informix on Unix and I had a DB where the ref. int was ignored by the original programmers because they were going to do everything themselves. Problem is they weren't the only programmers and things don't stay static. It was a night mare. So I look at it as this is the "way it should work for everyone who uses this DB and no one gets around that. In the case of cascade delete I certainly don't use it on everything but in the obvious case of where the business rules allow a record to be deleted - use it so that all of the records child tables are cleaned up when it is deleted. As far as cascade updates - they aren't needed when using auto # keys but certainly are when using "natural keys", e.g. keys that can be broken and will need to fixed because the system didn't use auto # keys to start with ;-) Referential integrity - its great! -----Original Message----- From: accessd-admin at databaseadvisors.com [mailto:accessd-admin at databaseadvisors.com]On Behalf Of Drew Wutka Sent: Saturday, February 01, 2003 3:53 PM To: 'accessd at databaseadvisors.com' Subject: RE: [AccessD] Cascade-delete (was: Estimating Help) Just my two cents here. I personally build my own business rules into the front end, or the middle tier. I don't like 'automatic' processes that I haven't built personally! <VBG> I think of it along the same lines of tghe B vs. UnB issue. It's there, and I can see it's use, I just prefer to control things on my own. Drew -----Original Message----- From: Jeanine Scott [mailto:jscott at mchsi.com] Sent: Saturday, February 01, 2003 3:05 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Cascade-delete (was: Estimating Help) Yes, I have been lucky. :) All the databases I've designed (with the exception of my very first) I've locked the user's totally out of the backend. They can only do what I give them security to do with the exception of one power user that I've really (and I stress really!) trusted. I supposed I'll be one of those that has to be burned before I follow the path of wisdom. :) I definitely can see that situation you described happening if you have a project where the client isn't willing to pay for the administrative interface. Jeanine Scott Sr. Systems Analyst Spindustry Systems 515-669-2074 jscott at spindustry.com CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential information. Any unauthorized review, use, disclosure, or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message including any attachments. -----Original Message----- From: accessd-admin at databaseadvisors.com [mailto:accessd-admin at databaseadvisors.com]On Behalf Of John W. Colby Sent: Saturday, February 01, 2003 2:43 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Cascade-delete (was: Estimating Help) Some of us are just luckier than others I guess. Never had a user rip the guts out of your database by trying to delete a client that they "weren't doing business with" at the moment. I should have such users! ;-) John W. Colby Colby Consulting www.ColbyConsulting.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com