From: "Daniel A. Morgan" <dmorgan@exesolutions.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: How to bypass referential integrity
Date: Tue, 05 Jun 2001 23:30:28 -0700
Organization: EXE
Message-ID: <3B1DCE04.923B1D45@exesolutions.com>
X-Mailer: Mozilla 4.7 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
References: <2_cT6.525$7d.60964@newshog.newsread.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Cache-Post-Path: yabetcha.drizzle.com!unknown@ava25.drizzle.com
X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/)
X-Complaints-To: newsabuse@supernews.com
Lines: 36


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