From: "Van Messner" <vmessner@bestweb.net>
Newsgroups: comp.databases.oracle.server
References: <2_cT6.525$7d.60964@newshog.newsread.com> <3B1DCE04.923B1D45@exesolutions.com>
Subject: Re: How to bypass referential integrity
Lines: 56
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Message-ID: <%_yT6.46428$gA.2013474@monger.newsread.com>
Date: Wed, 06 Jun 2001 23:26:51 GMT
NNTP-Posting-Host: 63.208.84.2
X-Complaints-To: Abuse Role <abuse@bestweb.net>, We Care <abuse@newsread.com>
X-Trace: monger.newsread.com 991870011 63.208.84.2 (Wed, 06 Jun 2001 19:26:51 EDT)
NNTP-Posting-Date: Wed, 06 Jun 2001 19:26:51 EDT
Organization: BestWeb (bestweb.net)


Thanks, Daniel.  I do use deferrable constraints, although that's not the
cause of the problem.  I can replicate the problem using tables without
deferrable constraints.  What I object to, I guess, is that using a feature
(index compression) blows away referential integrity without any warning.

Van


"Daniel A. Morgan" <dmorgan@exesolutions.com> wrote in message
news:3B1DCE04.923B1D45@exesolutions.com...
> Van Messner wrote:
>
> > Here's the setup.
> > Table A has an primary key AKEY
> > Table B has a primary key BKEY
> > Table INT is an intersection between A and B.  INT has a column called
 AKEY
> > which is foreign keyed back to the same column in A.  INT has a column
> > called BKEY which is foreign keyed back to the same column in B.  In
 table
> > INT the primary key is the columns AKEY and BKEY.  There are regular
 b-tree
> > indexes on AKEY and on BKEY in INT.  A standard setup.
> >
> > If you put data into A, B and INT, then deleting data from A or B when
 child
> > rows exist in INT causes an Oracle error.
> >
> > BUT now compress the index on AKEY in INT and compress the index on BKEY
 in
> > INT.  You can then delete rows in either A or B or both where child rows
> > exist in INT, leaving orphan rows behind in INT and Oracle gives no
 error
> > message.
> >
> > Van
>
> Look into the documentation on DEFERRABLE  FOREIGN KEY constraints. For
 example:
>
> ALTER TABLE xyz
>     ADD (CONSTRAINT xyz_abc_fk
>     FOREIGN KEY (xyz)
>     REFERENCES abc (abc_id) DEFERRABLE);
>
> The foreign key is not enforced until a commit is issued.
>
> That is the good news. The bad news is that incremental commits are often
> impossible making for potential rollback segment problems with large
 loads.
>
> Daniel A. Morgan
>
>