Re: How to bypass referential integrity

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 - 01:30:28 CDT