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<br />, tbl.name Table<br />, col.name Column<br />, col.default_value the_offending_default_value<br />from ci_application_systems fol<br /> join<br /> ci_folder_members fm<br /> on (fm.folder_reference = fol.id)<br /> join<br /> ci_table_definitions tbl<br /> on (fm.member_object = tbl.id)<br /> join<br /> ci_columns col<br /> on (col.table_reference = tbl.id)<br /> join <br /> ci_domains dom<br /> on (col.domain_reference = dom.id)<br /> left outer join<br /> ci_attribute_values atv<br /> on ( atv.domain_reference = col.domain_reference<br /> and atv.low_value = col.default_value<br /> )<br />where fol.name = 'CONNEXYS (1)'<br />and col.default_value is not null <br />and atv.low_value is null -- only show records where we did not find a matching Allowable Value<br />/<br />
Arne, I did find another potential violation:
- Domain: JANEE
- Table: FET_FEITEN
- Column: HANDMATIG_TE_ZETTEN
- Offending Value: N
- Building Check In protection into Oracle Designer/Oracle SCM – Check In only by the user that did the Check Out
- Improvements in Oracle SCM Check Out and Check In mechanism (better Oracle Designer Source Code Control)
- Oracle Designer – Keyword Expansion upon Check Out
- Oracle Designer Check In – synchronize P_VERSION argument
- Using unique function based index as check constraint