|
A domain constraint is a restriction (a form of data quality) that applies to all data values stored in all columns based on a given domain.
A restrictive domain constraint checks each value that a user submits for a column based on the domain. If the value violates the restriction, processing is blocked and the domain constraint gives an error message.
Example
"A person's first name must be entered with an initial capital letter followed by lowercase-only."
A restrictive domain constraint may be used to check each first name value submitted by a user. If the value does not meet the condition, the domain constraint will block processing and issue an error message, for example, "A person's first name must be entered with an initial capital letter followed by lowercase-only."
|
A corrective domain constraint automatically performs some operation on each value that a user submits for a column based on the domain. This way, the domain constraint guarantees that all values entered satisfy a restriction.
A corrective domain constraint typically operates in silence: it does not give a message. However, if you specify a Message, this message will be shown as a message of type Warning (and not: Error, as is the case with restrictive constraints) each time that the domain constraint actively corrects a value entered.
Example
"A person's first name must be entered with an initial capital letter followed by lowercase-only."
A corrective domain constraint may be used to check each first name value submitted by a user. If the value does not meet the condition, the domain constraint corrects it. This way, all the resulting data will meet the restriction. In this case, the corrective domain constraint will capitalise the first letter if it was not a capital letter, and it will change the remaining characters to lowercase if they were capital letters.
|
To define a domain constraint:
| 1. | Find the Constraints, Domain Constraints node in the Model and Rules tab of the Project Catalog. |
| 2. | Right-click on this node and choose New Domain Constraint from the context menu. Alternatively, you can choose Define, Constraints, Domain from the main menu. |
| The Domain Constraints window is opened. |
| 3. | Identify the domain that the constraint is for in the Domain Name field. At runtime the constraint will check all values in all columns based on that domain. |
| 4. | Enter a unique name for the domain constraint in the Constraint Name field. |
| 5. | If the domain constraint is to be restrictive, in the Do Not Allow That field, write a SQL condition expressing what is not allowed, using the DOMAIN keyword as a placeholder for the runtime values that a user attempts to input. For example, to block any use of colons (:) and semi-colons (;) in a domain, write: |
INSTR(DOMAIN,':') <> 0
OR INSTR(DOMAIN,';') <> 0
|
|
| In this case, write an explanatory Message to the end user that will be displayed when she attempts to enter a value that is blocked by the constraint. |
| 6. | In the domain constraint is to be corrective, in the Make Sure That field, write a SQL condition expressing what must automatically replace the input value, again using the DOMAIN keyword as a placeholder for the runtime values that a user attempts to input. For example, to make sure that any value is a multiple of 0.5, write: |
| You can also combine a value for Do Not Allow That (Step 5) and a value for Make Sure That (Step 6). If you do, any values that meet the Do Not Allow That condition will be subjected to the Make Sure That correction. Click Save to confirm. |
| 7. | Click the Check button. |
| If the domain constraint is correct, the checker will set the Correct = Yes flag. |
| If the constraint is not correct, a message is shown with an asterisk (*) at the position of the syntax error. Correct your work and repeat Steps 5 (or 6) and 7 until the constraint is correct. |
|
See also
Table constraints
Transitional constraints
Constraint messages
|
