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: joel garry <>
Date: Thu, 07 Jun 2007 16:08:00 -0700
Message-ID: <>

On Jun 7, 9:40 am, Ed Prochak <> wrote:
> 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?

Funny, you put your finger right on it! I'm not entirely sure (actually, the purge routines are broken for the amount of data and users don't have a performance issue so I haven't fixed them), but at one point I got the gist that Oracle had tried to sell them a DW solution right after totally messing them up with licensing (I think an O partner had not paid O and just kept the money, among other things). So I wasn't allowed to mention DW, and wound up writing an ETL routine that basically creates them on demand for any given floating time window. And of course, since they actually are given to just a few secretaries to run, it eventually became "push this button and several hundred spreadsheets will magically come out ready to email." The DSS people have other issues and wind up stovepiped anyways.

Also, you may recall around the turn of the century everything was moving towards centralization as the distributive database movement petered out, and Oracle was selling how new features would help centralize everything to one db. HAHA! Now of course the next big thing is Corporate Performance Management Working Against A Single Source Of Truth That Spans From Past To Present And Has The Latest Forecasts And Actions Plans For The Future With Access To On-Demand Reports With Up-To-The-Moment Timeliness And Accuracy. Somehow gotten through periodic MS-SQL sucks into cubes. The existing routines continue with minor mods until that all gets worked out.

> 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.

Actually, I have the worst, some constraints in the app, some in the db, some in modifications to the app, some not there at all and can't be because they'd break the app, and conflicting requirements. Doesn't everybody? :-)

I recently went through an interesting "reprint invoices" exercise too. The app had been modified to include what is currently on backorder, reprinting later would have different stuff. Not so much of a problem when the old one is in a file cabinet in the next room, but when ordering and invoicing get separated by many miles and the order people have to handle the customers inquiring about backorders on the invoices... sorry, no flashback on this one. One of the constraints is whether a customer is allowed to have backorders, which of course can change over time and is subject to exceptions including by line on the order... not to mention, ambiguity over what a backorder really is...

> Ed
> (little improvements here, slowly but surely

The ratio of steps forward to slides backward keep changing... :-)


-- is bogus.
" at the county fair will include fried chicken sandwiched
between Krispy Kreme donuts."
"That can't be real."
"It is."
"Someone is growing their own."
"Tomatos?" - heard on radio.
Received on Thu Jun 07 2007 - 18:08:00 CDT

Original text of this message