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? |
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
