Nonexisting Oracle Database Constraint Violated

Question

I have been assigned to work on an application that has two tables, let's call them MY_TABLE_ONE and MY_TABLE_TWO. When a user enters data in a form, the data is entered into MY_TABLE_ONE. After this is completed, a database package is triggered, which essentially does the following:

DELETE FROM MY_TABLE_TWO;

INSERT INTO MY_TABLE_TWO
mto.COLUMN_ONE,
moto.COLUMN_TWO,
mott.COLUMN_THREE
FROM MY_TABLE_ONE mto, MY_OTHER_TABLE_ONE moto, MY_OTHER_TABLE_TWO mott
WHERE mto.OTHER_TABLE_ONE_UUID = moto.UUID
AND mto.OTHER_TABLE_TWO_UUID = mott.UUID;

For some reason, this package began failing on the insert with a message of:

ORA-00001: unique constraint
(MY_SCHEMA.MY_TABLE_TWO_UK01) violated
ORA-06512: at "MY_SCHEMA.MY_PACKAGE", line 2
ORA-06512: at line 1

Looking up the constraints on MY_TABLE_TWO, there is no MY_TABLE_TWO_UK01 constraint. There is however a MY_TABLE_TWO_UK constraint which requires the 3 inserted columns to be unique. However, if I run the following query:

SELECT mto.COLUMN_ONE, moto.COLUMN_TWO, mott.COLUMN_THREE, COUNT(*) AS COUNTER
FROM MY_TABLE_ONE mto, MY_OTHER_TABLE_ONE moto, MY_OTHER_TABLE_TWO mott
WHERE mto.OTHER_TABLE_ONE_UUID = moto.UUID
AND mto.OTHER_TABLE_TWO_UUID = mott.UUID
GROUP BY mto.COLUMN_ONE, moto.COLUMN_TWO, mott.COLUMN_THREE
ORDER BY COUNTER DESC;

then all of the counts are 1. So it doesn't look like that constraint is being violated.

Does anyone have an idea of what's going on? I am not sure where that 01 is coming from for the constraint name. And I can't find where the constraint is violated.

Answer 1

it's an index

select * from all_ind_columns where index_name = 'MY_TABLE_TWO_UK01';