Defining Constraints

Defining 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.

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.

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:

ROUND (DOMAIN * 2) / 2

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.

7.	Check the constraint.

Defining a Table Constraint

To define a table constraint:

1.	Find the Constraints, Table Constraints node in the Model and Rules tab of the Project Catalog.

2.	Right-click on this node and choose New Table Constraint from the context menu. Alternatively, you can choose Define, Constraints, Table from the main menu.

The Constraints window is opened.

3.	Minimally, enter a unique name for the constraint in the Constraint Name field, then enter the SQL statement in the Statement field on the right. Press Save to save work.

4.	If you have written a restrictive constraint, your Constraint SQL code will start with the SELECT keyword. In this case, write an end user message in the Message field. If the constraint blocks processing at runtime, this is the explanatory message that will be presented to the user.

5.	Press the Check constraint to have the constraint SQL code validated. If this code is found valid, USoft Definer will set the Correct flag. Press Save to save work.

To write a transitional constraint, you also need to either use the OLD() function in the constraint SQL code, or set transition properties, or both.

To write an invocative constraint, you will need to write a component. The Constraint SQL code in this case will start with the INVOKE keyword, or with the SELECT keyword followed by function call syntax invoking a component.

6.	Check the constraint.