DOMAIN Keyword

Previous Next

When you define a domain constraint, you cannot type SELECT/ INSERT/UPDATE/DELETE ... FROM ... WHERE in your SQL statement. This is because neither the tables nor the columns in which to look are known in advance. Also, you cannot type the name of the domain in the SQL statement itself. Instead, you can use the DOMAIN keyword. You can use this keyword in all places where SQL allows you to specify column names.

Example 1:

Suppose that persons' first names are only accepted if they are entered with an initial uppercase letter. This can be taken care of by the following (restrictive) constraint, entered in the Do Not Allow That field:

SUBSTR(DOMAIN, 1, 1) != UPPER(SUBSTR(DOMAIN, 1, 1))

OR SUBSTR(DOMAIN, 2) != LOWER(SUBSTR(DOMAIN, 2))

 

This constraint can also be written as:

 

DOMAIN != UPPER(SUBSTR(DOMAIN, 1, 1)) || LOWER(SUBSTR(DOMAIN, 2))

 

This example works on all supported data sources because the Rules Engine converts these functions internally.

Example 2:

Suppose that certain data is to be changed to initial uppercase letters automatically. Instead of the condition specified in Example 1, the following SQL statement should then be entered in the Make Sure That field:

UPPER(SUBSTR(DOMAIN, 1, 1)) || LOWER(SUBSTR(DOMAIN, 2))

 

Example 3:

Suppose that a particular value in the database must be a multiple of 4. This is taken care of by the following restrictive constraint, entered in the Do Not Allow That field:

DOMAIN/4 != ROUND(DOMAIN/4,0)