Re: How to bypass referential integrity

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_at_exesolutions.com> wrote in message news:3B1DCE04.923B1D45_at_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
>
>
Received on Wed Jun 06 2001 - 18:26:51 CDT