Path: news.f.de.plusline.net!news-fra1.dfn.de!news.tele.dk!feed118.news.tele.dk!postnews.google.com!news4.google.com!out02b.usenetserver.com!news.usenetserver.com!in01.usenetserver.com!news.usenetserver.com!news-xfer.nntp.sonic.net!news.astraweb.com!border2.newsrouter.astraweb.com!sn-xt-sjc-05!sn-xt-sjc-08!sn-xt-sjc-01!sn-post-sjc-01!supernews.com!corp.supernews.com!not-for-mail From: Walt Newsgroups: comp.databases.oracle.server,comp.databases.oracle.misc Subject: Re: not using foreign keys? Date: Wed, 06 Jun 2007 09:36:10 -0400 Organization: Corwood Industries Message-ID: <136de2ari5u989c@corp.supernews.com> Reply-To: walt_askier@SHOESyahoo.com User-Agent: Thunderbird 1.5.0.12 (Windows/20070509) MIME-Version: 1.0 References: <1180979621.128496.281540@n4g2000hsb.googlegroups.com> <46644eb3$0$16320$88260bb3@free.teranews.com> In-Reply-To: <46644eb3$0$16320$88260bb3@free.teranews.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: abuse@supernews.com Lines: 64 Xref: news.f.de.plusline.net comp.databases.oracle.server:199013 comp.databases.oracle.misc:79861 Brian Peasland wrote: > spacemarine@mailinator.com wrote: >> i have a question -- is the practice of using FKs to maintain >> referential integrity an argued (for/against) practice? im now on an >> enterprise banking project, and the db designer does not use FKs, he >> feels it creates a "closed system", and prefers to have the coders >> manage constraints via procs. >> >> id never seen this before, but he said its an age-old argument (w/ >> merits) and one he adheres to. is this so, or is it an unusual >> personal preference? > > Sounds like its time to get someone else on the project. Always let the > Oracle engine enforce referential integrity through FK constraints. > Doing so with a stored proc is not the way to go. How does the developer > guarantee that the proc gets run when a row of data is INSERTed into the > table? There are ways. Create procs that handle insert/update/delete and grant execute rights to the app, without giving the app insert/update/delete rights to the table itself. With this approach, the only way for the app to insert/update/delete is through the procs. (Of course, this doesn't prevent the table owner or a DBA from breaking the data integrity rules, just the app - perhaps this is what SM's boss is alluding to with the "closed system" phrase: he wants to be able to break the rules if it suits him) Now, this doesn't argue against FK, it's just a good approach when there are integrity rules you want to enforce that go beyond what you can do with the standard built-in constraints. In particular, procs and triggers cannot see uncommitted data in other sessions, so in a multi-user environment they are not enough to ensure integrity. For instance: User A wants to insert a child record that references a parent and the proc checks to make sure it's there - so far so good. User B wants to delete a parent record, and the proc checks to make sure it has no dependent records. If both of them are doing this at the same time for the same parent, both checks will pass, but you'll wind up with a child with no parent. A FK prevents this (one of the users will receive an error on commit) , all the procs in the world won't. Without the FK constraint, you have a conduit for *bad data*; single-user testing will never produce the bad results, it will only happen with multiple users (and since far too many apps are released without multi-user testing, this problem may not appear until it's in production. oops. ) And /when/ (not if) it happens the coders will scratch their heads and say "but that can't happen, we have code that prevents it, watch this test...". SM, to put it bluntly, your boss doesn't understand data design. You are in a world of hurt. Best of luck, you'll need it. > A worse approach would be to let the application handle the referential > integrity. But don't get me started on that.... Agreed. Don't get me started either.... //Walt