Sunday, May 27, 2012

Finding table for Oracle constraint violation

When Oracle reports a constraint violation, you might get something unhelpfully cryptic like:

ORA-00001: unique constraint (FOO.SYS_C003567231) violated

The constraint is system-generated, based on UNIQUE being specified in the DDL, so the name is unhelpful. With no other information, finding the affected table can be difficult. You can query for the table with the following SQL:

select constraint_name, table_name
from user_constraints
where constraint_name='SYS_C003567231

This is a handy query to know, so I'm noting it here for my future reference. It's also described in a number of other blogs like this one.


4 comments:

  1. Thank you, this is very useful.

    ReplyDelete
  2. That's Brilliant ideas for properties investment in Australia.Property Development Feasibility Software is so good.

    ReplyDelete
  3. that's nice blog for properties investment in Australia.Residential Property Developers is so good.

    ReplyDelete
  4. This article is so good for real estate investments.Property Development Feasibility Software is so good for investment.

    ReplyDelete