Quality Check for Domains in Oracle Designer – fighting problematic AVCON check constraints

One of the things that you never expect to go wrong will of course go wrong anyway, as I experienced today at one of my customers’ sites. We ran into an issue with a Check Constraint generated by Oracle Designer for enforcing the allowable values for a columns as specified in a domain. For some unintended reason, the default value for the column happened to be a value not defined in the domain. How stupid is that?

Well, as it apparently can happen – Oracle Designer does not protect you from that fate. In this article I dwell a little on avoiding the issue of having a mismatch between the column’s default value and the set of allowable values as specified by the domain. By the way, this article is dedicated to Arne.

One way of dodging this bullet is by not generating the Check Constraint for the Domain’s allowable values. Probably this is a good idea anyway for all domains except the extremely static ones, like YES_NO and GENDER. From the On Line Help of Oracle Designer on switching off Domain Check Constraint (AVCON…) generation:

“Validation against check constraints (hard-coded)

By default, when you generate a table whose columns or associated domains are defined with allowable values, a check constraint is generated to enforce the rules on the database. The check constraint is generated as part of the table structure itself and therefore guarantees that the rule is always enforced, even if multiple applications insert or update records in the column. The allowable values can optionally be hard-coded in client applications that insert or update data in the underlying columns to perform validation on the client.

A valid value check constraint is generated when the following conditions are true:

• the column property Dynamic List?= No
• the table is generated with the Generate Valid Value Constraints option selected
• the target for generation is an Oracle or DB2/2 Workbench database, or script

If you define valid values against a domain, a check constraint is generated for each column that is associated with the domain. If the target database is Oracle, valid value check constraints are prefixed AVCON_. For DB/2 Workbench the prefix is AV, due to the 18 character naming restriction.

During generation, valid value constraints (prefixed with either AVCON_ or AV) on existing tables are automatically dropped from the database and new ones are then created in their place. This ensures that the valid value constraints on the database always match the allowable values held in the repository.
During database capture, valid value constraints (prefixed with either AVCON_ or AV) are ignored. They do not need to be captured as new ones are always created during table generation.

Note: Server Generator generates constraint names internally to ensure uniqueness.”

Alternatively, we can check whether we have accidentally created a mismatch between the Domain’s Allowable Values and the Column’s Default Value.

select dom.name Domain
,      tbl.name Table
,      col.name Column
,      col.default_value the_offending_default_value
from   ci_application_systems fol
       join
       ci_folder_members fm
       on (fm.folder_reference = fol.id)
       join
       ci_table_definitions tbl
       on (fm.member_object = tbl.id)
       join
       ci_columns col
       on (col.table_reference = tbl.id)
       join 
       ci_domains dom
       on (col.domain_reference = dom.id)
       left outer join
       ci_attribute_values atv
       on ( atv.domain_reference = col.domain_reference
            and    atv.low_value = col.default_value
           )
where  fol.name = 'CONNEXYS (1)'
and    col.default_value is not null 
and    atv.low_value is null -- only show records where we did not find a matching Allowable Value
/

Arne, I did find another potential violation:

• Domain: JANEE
• Table: FET_FEITEN
• Column: HANDMATIG_TE_ZETTEN
• Offending Value: N