The Oracle (tm) Users' Co-Operative FAQ

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


Author's name: Martin Haltmayer

Author's Email: Martin.Haltmayer@0800-einwahl.de

Date written: 14.06.2001

Oracle version(s): 8.1.7

For deferred unique and pk constraints you may use non-unique indexes. So which index serves which constraint?

Back to index of questions


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 as index_owner
            , o.object_name as index_name
            , n.name as 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.


Further Reading: N/A


Back to top

Back to index of questions