DELETE is one of the easist ways to screw up a database that doesn't have proper RI declared. Clients all the time ask to "delete" Customer X because they no longer work with them, business closed, got bought, or something.
Months later, they want to re-run reports for years past, and can't understand why the numbers don't tie out. Nearly all the entities in my apps have an Active/Inactive flag so they no longer show up on dropdowns and picklists, but still exist in the database for historical purposes. If there's a big enough demand, there's an admin function to remove "unused" records, but usually that's just a cleanup issue for the DBA. As Bill said, RI rules in the DB should just be a backup for database integrity, and especially important if DBs can be accessed from other apps (like Excel!). I'm a fan of "Forbid" rules that prevent deletion of records "in use," but not "delete cascade" that makes little problems into big ones; I think a delete cascade should be a business object procedure, not one at the DB level. And of course, as the consultant says, "It depends." On Wed, Nov 1, 2017 at 3:54 PM, Kurt Wendt <[email protected]> wrote: > Interesting discussion - considering a situation here at my job just > today/yesterday. > > An email from a manager claiming that since this one employee is no longer > with the co. (although the email was late since the guy left about 4 weeks > ago) - was asking to have this User removed from these various systems. So - > I responded yesterday that I had deleted the user record from this one system. > > Then - this buddy of mine, another programmer here - he was out yesterday, so > he asks me about the deletion of the record. He was worried that the deletion > of the record would cause problems if there are FK constraints! I told him he > need not worry - since for the most part in the system I did it - there > really are mostly no FK's in there. Of course, I know there are a couple - > but, nothing that would be impacted by the removal of a user from a table. > > So - yes - I can relate. > > -K- > > -----Original Message----- > From: ProfoxTech [mailto:[email protected]] On Behalf Of > [email protected] > Sent: Wednesday, November 1, 2017 3:39 PM > To: [email protected] > Subject: Re: How many of you use foreign key constraints and References in > your database schema? > > On 2017-11-01 15:21, Bill Anderson wrote: >> Mike, >> >>>> Where's your preference in design with these in mind?<< >> >> My philosophy is that the back end database, whatever it is, should be >> a dumb as a rock. A database should only be concerned with its >> internal integrity and no more. Anything else is pretty much geared >> toward vendor lock-in. >> >> But having noted that, FK constraints are absolutely a part of the >> data integrity. So yes, use 'em. >> >> Views? Sure, that's basically the only way to communicate. Business >> rules? >> Nope, stay away. That's for middleware. >> >> Bill Anderson > > > Hi Bill! > > I agree completely. > > --Mike > > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/CACW6n4uALpGxJJAetHGAzZT3tK-pB0dMRxD_XjD8=a+ovwj...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
