Field and Record Validation Rules
Field and record validation rules control how and the type of data that you can enter in database table fields and records. Validation rules provide the following advantages:
Provide a way to enforce business rules consistently.
Help you write less code.
Are enforced for all users of the database table, regardless of the requirements of the application.
When using validation rules, the values entered in fields and records are compared against the rule expressions that you specify. If an entered value does not meet the requirements of the rule expression, the value is rejected.
For example, you might create a rule that compares the value of a postal code field against a separate table containing the postal abbreviation codes for your country or region and rejects any value that does not exist as a valid postal code abbreviation.
Tip
Avoid creating field or record-level rules that are application-specific. Instead, create and use field and record-level rules to enforce data integrity and business rules that always apply to the data in your database, regardless of how or who accesses the data.
Note
Validation rules are stored in the database (.dbc) file. Removing or deleting a database table removes and deletes all field and record-level rules associated with that table. However, stored procedures referenced by the removed or deleted validation rules remain.
The following sections contain more information about working with validation rules:
Choosing Between Field and Record Validation Rules
Checking Field and Record Validation Rules
Choosing Between Field and Record Validation Rules
You can use a field-level validation rule when the following apply:
You want to control the type of information a user can enter in a field.
You can validate the data in a field independently from any other entry in the record.
You want to compare values entered in a field against the values in another table.
For example, you can use a field-level validation rule to make sure that a user does not enter a negative number in a field that requires a positive value.
You use record-level validation rules when the following apply:
You want to control the type of information a user can enter in a record.
You want to compare the values of two or more fields in the same record to make sure comply with business rules for the database.
For example, you can use a record-level validation rule to make sure that the value in a field is always greater than another in the same record.
Checking Field and Record Validation Rules
Field and record validation rules are active even when data is buffered. Generally, field validation rules are checked when the field's value changes; record validation rules are checked when the record value changes.
Note
If a field or record value has not changed, validation rules are not checked. When no values have changed, you can navigate fields and fields without validating data.
The following table describes more specifically when Visual FoxPro checks field validation rules depending on the method of data entry.
| Changing values using | Window or command | Field validation rule is checked |
|---|---|---|
User interface |
Browse window Form Other window |
When moving off the field. |
Commands that do not specify fields |
APPEND CommandAPPEND GENERAL CommandAPPEND MEMO CommandBROWSE CommandCHANGE CommandDELETE CommandEDIT CommandGATHER Command |
When changing the field value. The rule is checked in the field order specified in the command. |
Commands that do not specify fields |
APPEND Command with BLANK clauseINSERT - SQL Command |
When appending or inserting the record. |
Commands that specify fields |
In the field order specified in the command. |
In contrast, Visual FoxPro checks record validation rules whenever the record pointer moves off the record, regardless of the method you use to enter data. In addition, in a browse window, the record validation rule is checked if you modify a record but do not move the record pointer, and then close the browse window. Visual FoxPro generates any error messages that occur, and then closes the browse window.
Warning
Do not include any commands or functions in your validation rules that attempt to move the record pointer in the current work area where validation rules are checked. For example, including commands or functions such as SEEK, LOCATE, SKIP, APPEND, APPEND BLANK, INSERT, or AVERAGE, COUNT, BROWSE, and REPLACE FOR in validation rules might check them recursively, creating an error condition.
Note
When a trigger is called, the Alias is always that of the cursor being updated, regardless of the Alias selected in the code that caused the trigger to fire.
When a record validation rule causes an error during a running application, you need to include error-handling code. Typically, this code does not permit the user to leave a form or change the active environment until the user corrects the error or cancels changes to the record.
See Also
Tasks
How to: Create Field Validation Rules
How to: Create Record Validation Rules