Oracle unique constraint violation referencing a constraint that doesn't seem to exist

Question

I'm receiving an error that I can't make sense of. The error is pretty straightforward:

ORA-00001: unique constraint (*schema*.*xyz_constraint*) violated

However, what's causing my confusion is the fact that no such constraint seems to exist. It's certainly not defined on the table; the DB in question has almost no referential integrity defined, and the particular table into which I'm inserting data has no key defined.

For what it's worth, I can't find the constraint anywhere in the database:

    select *
    from all_constraints 
    where constraint_name like '%xyz_constraint%'

Is there anything I'm overlooking? Thanks.

Answer 1

I had the exact same error and it was not a permissions issue. Rather, the entity turned out to be an INDEX not a CONSTRAINT and I found it with:

SELECT * FROM ALL_INDEXES WHERE INDEX_NAME LIKE '%XYZ_UK1'

...because it did not exist in ALL_CONSTRAINTS or DBA_CONSTRAINTS. Confusing how the error message is the same. I'm running Oracle 11g.

Answer 2

This happens when the constraint belongs to another user and you don't have permissions to it.

Try looking it once again for now from SYS perspective

Answer 3

Schema object names are almost always stored in upper-case. Try

select *
from all_constraints 
where constraint_name like '%XYZ_CONSTRAINT%'

ORA-00001 usually happens for duplicate primary keys. Are you sure you don't have one on the table?