These queries are part of my library of command line scripts to find out constraint info. You may find them useful.
--Constraints on a table
SELECT owner, table_name
,constraint_name
,DECODE(constraint_type, 'C', 'Check'
, 'P', 'Primary'
, 'U', 'Unique'
, 'R', 'Foreign'
,constraint_type
) const_type
,DECODE(constraint_type, 'R', r_owner||'.'||r_constraint_name
, NULL) referring_to_const
,search_condition
,status
,index_owner||'.'||index_name constraint_index
FROM dba_constraints
WHERE owner = UPPER('&&p_owner')
AND table_name = UPPER('&&p_table')
AND constraint_name NOT LIKE 'SYS%'
/
--Which columns the constraint is on
SELECT owner, table_name, constraint_name, column_name, position
FROM dba_cons_columns
WHERE constraint_name = UPPER('&&p_specific_const')
ORDER BY owner, table_name, constraint_name, position
/
- Alwyn
