Sunday, December 14, 2008

Constraint Information

While performing DML operations, Some times we experienced constraint violation. Then we searching the details of that culprit constraint :)

Here is a simple script that help us a lot to handle such situation:

SELECT uc.owner , uc.constraint_name
, DECODE(uc.constraint_type,'C','CHECK','P' ,'Primary key','R','Referential','U'
,'Unique Key') constraint_type , uc.table_name
, ucc.column_name , uc.r_owner , uc.r_constraint_name
, ( SELECT 'Table : '||uc1.table_name||' Column: '|| ucc1.column_name
FROM user_constraints uc1,USER_CONS_COLUMNS ucc1
WHERE uc1.constraint_name=ucc1.constraint_name
AND uc1.constraint_name=uc.r_constraint_name
) R_description
FROM user_constraints uc,USER_CONS_COLUMNS ucc
WHERE uc.constraint_name=ucc.constraint_name
AND uc.constraint_name IN('FK625EF6937B99960');

The only thing you need to change the constraint name 'FK625EF6937B99960'

No comments: