need a "Dup Key" Exception name

[sweetleaf]

Hi all,

Can anyone tell me what i can use to trap when a duplicate composite key has been violated?
I've already tried the dup_val_on_index oracle exception, as well as trying to trap the actual sqlcode(-00001), and
both trap duplicate indexes instead of PK's.
As expected this is not the solution i need as records which do not violate the pk still do not get inserted b/c of one column(having an index).

any help is greatly appreciated

 

[carp]

Let me see if I have the scenario right -
You have a table with a composite PK on columns AA and BB. One or both of these columns also has a unique index built on it.

If this is the case, then your choices would seem to be:

1. Drop the index on the single column.
2. Recreate the index on the single column so it is non-unique.
3. Redesignate your PK.

- Since both columns are part of a PK, neither can allow NULL values.
- If one or the other column must be unique and cannot allow a null value, then that column fulfills the requirements for a PK; you could drop the composite PK and use the single column.
- If the column doesn't really have to be unique, then you can drop the unique index.

On the other hand, if I have completely misunderstood your situation, then you need to wait for another post or expand on your description.