Re: Which rows broke my constraint?

Oracle does not automatically record the key values that are involved in a unique constraint violation nor does it provide a built-in method of doing this for insert or update statements like it does with the exception clause of the create index command.

You must enclose the offending code in a pl/sql block with an exception clause that records the key columns values if you want this, or prior to doing the insert or an update that changes the value for one of the columns that make up the key you can issue a select for the key. This is potentially expensive, as it is extra work that your process will be doing just to find potential exceptions.

Lee Pollington <lee_at_cursci.co.uk> wrote in article ...
> I want to know if Oracle keeps the RowID's or something
> of rows that break a unique index. I have a table which
> has a unique index across 3 columns: a foreign key and
> two number columns. If the uniqueness of this index is broken
> by an insertion/update to this table an exception is raised as
> a duplicate value on an index. I want to be able to find out from
> Oracle which rows conflicted with the update/insertion.
>

.... removed rest to save bandwidth
>
Received on Wed Aug 19 1998 - 08:23:49 CDT