Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: not using foreign keys?

Re: not using foreign keys?

From: Ed Prochak <>
Date: Thu, 07 Jun 2007 16:40:05 -0000
Message-ID: <>

On Jun 6, 7:11 pm, joel garry <> wrote:
> On Jun 6, 2:55 pm, Walt <> wrote:
> > DA Morgan wrote:
> > > Brian Peasland wrote:
> > >> A worse approach would be to let the application handle the
> > >> referential integrity. But don't get me started on that....
> > > What we somehow need to educate these front-end developers about is that
> > > front-ends are technically incapable of enforcing referential integrity.
> > > They give the appearance of doing so but actually do not and can not.
> > > No front-end designable is capable of keeping someone accessing the
> > > data with any other tool from destroying it be that batch loads with
> > > SQL*Loader, DBAs with SQL*Plus, or a host of tools that use ODBC or JDBC.
> > Well said.
> > I think in this case we're looking at a data designer who is *planning*
> > on wanking the data with one of those tools. Iron clad data integrity
> > rules make for a "closed system" that you can't bend to your whim using
> > sql*plus or your chainsaw of choice.
> > What's needed here is a DBA who understands the value of data, and also
> > understands that he shouldn't be allowed to break the rules of data
> > integrity any more than the lowly applications programmers.
> > //Walt
> Playing devil's advocate for a moment, I see that there is some
> relatively low level of rule where it becomes simply beyond the
> capabilities of the database to constrain. So, wouldn't there be some
> design value to not having two completely different constraint
> mechanisms (one in the database that needs to be undone at times
> anyways, and the one that can handle more complex rules)?
> (Lest anyone misunderstand, I'm with the advocates of "as much in the
> db as possible." However, not 5 minutes ago I was working on some ETL
> stuff that requires the same data to be viewed with both historical
> and current integrity constraints. Yes, it is wanked with
> sql*loader.)
> jg
> --
> is bogus.
> Torture that data until it gives you what you want! Bwahahaha!

Then why isn't the Historical data in a data warehouse instead of in production?

To some extent I see where you are coming from, but that case just shows that, even as flexible as Oracle is, there are limits. And that is a different issue: managing changes in requirements over time.

We had a similar issue in our database recently in the code to purge old old data. There was a program which deleted customers and their vehicles if there was no activity after a certain period of time. Trouble was the Vehicle data was needed to reprint the invoices. Our system is fairly large and no one knew which application deleted the vehicles. So in our latest release, I installed the FK constraint from invoice header to the vehicle table. So we stopped the errant application in its tracks. None of the programmers that have been here a long time believed there was a bug in the apps.

So I say FK constraints provide a much larger benefit than they do a hindrance.

(little improvements here, slowly but surely) Received on Thu Jun 07 2007 - 11:40:05 CDT

Original text of this message