2012-01-04

Constraint name generated

Generated constraint names makes development, testing and production environment error messages vary. And those reported SYS_C006206630 could be more informative. To identify those generated constraint names *_constraints views has the information available at generated column. The constraint search_condition is a long typed column and thou hard to use. Working with long columns document gives tools to convert the search column to varchar2. This query reports generated named constraints in users schema.
WITH xml AS (
          SELECT DBMS_XMLGEN.GETXMLTYPE(
                     'select co.constraint_name
                           , co.table_name
                           , cc.column_name
                           , co.constraint_type
                           , co.r_constraint_name
                           , co.search_condition  
                        from user_constraints co
                        left outer join user_cons_columns cc
                          on co.constraint_name = cc.constraint_name 
                       where co.generated = ''GENERATED NAME''')
                    AS xml
          FROM   dual
          ), concol as (
  SELECT extractValue(xs.object_value, '/ROW/TABLE_NAME')       AS table_name
  ,      extractValue(xs.object_value, '/ROW/COLUMN_NAME')      AS column_name
  ,      extractValue(xs.object_value, '/ROW/CONSTRAINT_TYPE')  AS constraint_type
  ,      extractValue(xs.object_value, '/ROW/CONSTRAINT_NAME')  AS constraint_name
  ,      extractValue(xs.object_value, '/ROW/R_CONSTRAINT_NAME')  AS r_constraint_name
  ,      extractValue(xs.object_value, '/ROW/SEARCH_CONDITION') AS search_condition
  FROM   xml x
  ,      TABLE(XMLSEQUENCE(EXTRACT(x.xml, '/ROWSET/ROW'))) xs
)
select cc.* 
  from concol cc
order by table_name, column_name,constraint_type
;

Give names to the reported constraints to your modeling tool. NOT NULL checks are not first in the list to be named. NOT NULL error messages are informative with the generated names also. But naming also those make the different (DEV,TST,PRD) schemas a bit more similar. SQL Developer Data modeler 3.1 EA3 has the possibility to name those also at relational model. Formerly it was only possible at physical model. If you feel like too much work modeling those at least change the reported names.

ALTER TABLE test RENAME CONSTRAINT SYS_C006206630 TO test_pk;


No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.