How can I identify which index represents which primary or unique key constraint?

The connection between constraints and the indexes which are used to check these constraints for the current user can be described by this query:

        select  --+ rule
          o.owner index_owner,
          o.object_name index_name,
          n.name constraint_name
        from sys.cdef$ c, dba_objects o, sys.con$ n
        where c.enabled = o.object_id
        and c.con# = n.con#
        and n.owner# = uid;

If you leave away the condition and n.owner# = uid you get all the constraints. You may further limit this query to your constraint name by adding the condition and n.name = 'your_constraint_name'.

Why can indexes and constraints be so different? In particular, you may use for example an index on columns (c, a, b) to enable a unique constraint on columns (a, b, c). Remember a constraint is a logical structure whereas an index is a physical one. So a unique or a primary constraint just describe the uniqueness. If (c, a, b) is unique then all other permutations are unique as well.

Further, these indexes may also be non-unique. You need this if you have a deferred constraint that is checked only at commit time. If you would insist on a unique index the attempt to insert duplicate values would fail before the commit although another command may have undone the duplicate entry.

This original query was contributed to comp.databases.oracle.server by Thomas Kyte.