Enforcing Business Rules

  • Article

You can enforce business rules for data entry by creating field-level and record-level rules, called validation rules, to control the data entered into database table fields and records. Field- and record-level rules compare the values entered against the rule expressions that you define. If the entered value does not meet the requirements of the rule, the value is rejected. Validation rules exist only in database tables.

Field- and record-level rules enable you to control the types of information entered into a table, whether the data is accessed through a Browse window, a form, or programmatically through the language. They allow you to consistently enforce the rule for a field with less coding than if you wrote the rule expression as code in a VALID clause on a form, or in a portion of program code. In addition, the rules you establish in a database are enforced for all users of the table, regardless of the requirements of the application.

You can also create candidate or primary indexes that prevent duplicate entries in a field, and triggers to enforce referential integrity or perform other actions when the data in your database is changed.

Knowing When Constraints Are Enforced

You choose database constraints based on the level at which you want to enforce a business or referential integrity rule, as well as the action that causes the constraint to be activated. The following table lists the data validation constraints in the order in which they are enforced by the Visual FoxPro engine, the level at which they are applied, and when the engine activates the validation.

Enforcement Mechanism Level Activated
NULL validation Field or column When you move out of the field/column in a browse, or when the field value changes during an INSERT or REPLACE.
Field-level rules Field or column When you move out of the field/column in a browse, or when the field value changes during an INSERT or REPLACE.
Record-level rules Record When the record update occurs.
Candidate/primary index Record When the record update occurs.
VALID clause Form When you move off the record.
Triggers Table When table values change during an INSERT, UPDATE, or DELETE event.

Constraints are activated in the order in which they appear in the table. The first violation of any validation test stops the command.

Candidate and primary indexes are explained in the section Controlling Duplicate Values.

Limiting Values in a Field

When you want to control the type of information a user can enter into a field, and you can validate the data in a field independently of any other entry in the record, you use a field-level validation rule. For example, you might use a field-level validation rule to ensure that the user doesn't enter a negative number in a field that should contain only positive values. You can also use a field-level rule to compare the values entered in a field against the values in another table.

You should not create field- or record-level rules that are application-specific. Use field- and record-level validation rules to enforce data integrity and business rules that always apply to the data in your database, regardless of who may access the data. For example, you might create a rule that compares the entry in the postal_code field of a table against a lookup table that contains the postal abbreviation codes for your country/region, and rejects any value that is not already present as a valid postal code abbreviation.

To create a field-level rule

  1. Open a table in the Table Designer.

  2. In the Table Designer, select the name of the field you want to set a rule for.

  3. Choose the dialog button next to the Rule box.

  4. In the Expression Builder, set up the validation expression and choose OK.

  5. In the Message box, type the error message in quotes.

  6. Choose OK.

    -or-

  • In the Table Designer, enter the rule expression in the Rule box in the Field validation area.

    -or-

  • Use the CHECK clause of the CREATE TABLE command.

    -or-

  • Use the SET CHECK clause of the ALTER TABLE command.

For example, the following code adds a field-level validation rule to the orditems table requiring that numbers entered into the quantity field be 1 or greater:

ALTER TABLE orditems 
    ALTER COLUMN quantity SET CHECK quantity >= 1

When the user attempts to enter a value less than 1, Visual FoxPro displays an error and the value is rejected.

You can customize the message displayed when the rule is violated by adding validation text to the field. The text you enter is displayed instead of the default error message.

To add a custom error message to a field-level rule

  • In the Table Designer, enter the error message you want in the Message box in the Field validation area.

    -or-

  • Use the optional ERROR clause with the CHECK clause of the CREATE TABLE or ALTER TABLE commands.

For example, the following code adds both a field-level validation rule for the orditems table requiring that numbers entered into the quantity column must be 1 or greater, as well as a custom error message:

ALTER TABLE orditems ;
   ALTER COLUMN quantity SET CHECK quantity >= 1 ;
   ERROR "Quantities must be greater than or equal to 1"

When the user attempts to enter a value less than 1, Visual FoxPro displays an error with the custom error message you defined, and rejects the failed value. You can also use the SET CHECK clause of the ALTER TABLE command with the optional ERROR clause to create a custom error message.

See Also

Controlling Display of a Field | Knowing When Field-Level Rules Are Checked | Working with Tables | Creating Default Field Values | Record-Level Rule Checking