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 later in this chapter 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, and rejects any value that is not already present as a valid postal code abbreviation.
To create a field-level rule
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.
Knowing When Field-Level Rules are Checked
Field-level rules are checked when the field’s value changes. Unlike triggers, field-level rules fire even if data is buffered. When you work with data in a Browse window, form, or other window, Visual FoxPro checks field-level rules as you move away from the field. If a field value has not been changed, the rule is not checked. This means that you are free to tab through fields without the system validating any of the data.
Field-level Rule Checking
Data entry method Window or command Field-level rule checked
User interface Browse window
Form
Other window
As you move away from the field, if the field value has changed. (If the field value has not been changed, the rule is not checked.)
As field value changes, in field definition order.
APPEND BLANK INSERT
INSERT - SQL
As the record is appended or inserted.
Commands that specify
fields UPDATE
UPDATE - SQL REPLACE
In the order in which fields are specified in the command.
Validating Record-Level Values
You use record-level validation rules to control the type of information a user can enter into a record.
Record-level validation rules typically compare the values of two or more fields in the same record to make sure they follow the business rules established for the database. For example, you can use a
record-level validation rule to ensure that one field’s value is always greater than that of another in the same record.
To create a record-level validation rule and custom error message
In the Table tab of the Table Designer, enter the rule and error message you want in the Rule and Message boxes.
-or-●
Use the CHECK clause of the CREATE TABLE or ALTER TABLE commands.
●
For example, you might want to ensure employees are 18 years or older when hired. The following code adds a record-level validation rule and error text for the employee table requiring that the date of hire entered into the hire_date column is greater than or equal to their birth date plus 18 years:
ALTER TABLE employee SET CHECK ;
hire_date >= birth_date + (18 * 365.25) ;
ERROR "Employees must be 18 years or older by date of hire"
If the user enters an employee record with an invalid date, Visual FoxPro displays an error with the custom error message you defined, and does not update the record.
You can also use the SET CHECK clause of the ALTER TABLE command to create a record-level validation rule. You should ensure that any rules specified for fields do not conflict semantically with the rules you define for the table. Visual FoxPro makes no attempt to compare the field-level and
record-level expressions for consistency.
Knowing When Record-Level Rules are Checked
Record-level rules, like field-level rules, activate when the record value changes. No matter how you work with data, whether in a Browse window, form, or other user interface window, or through
commands that alter data, Visual FoxPro checks record-level rules as you move the record pointer off the record. If no values within the record have changed, the record-level rule is not checked when you move the record pointer. You are free to move through records without the system validating any of the data.
If you modify a record, but don't move the record pointer, and then close the Browse window, the rule is still checked. You're warned of any errors that occur, and the Browse window is closed.
Caution Do not include any commands or functions in your validation rules that attempt to move the record pointer in the current work area (that is, in the work area whose rules are being checked).
Including commands or functions such as SEEK, LOCATE, SKIP, APPEND, APPEND BLANK, INSERT, or AVERAGE, COUNT, BROWSE, and REPLACE FOR in validation rules may cause them to trigger recursively, creating an error condition.
Unlike triggers, record-level rules fire even if data is buffered. When a record-level rule fires during a running application, you need to include error handling code. Typically, this will mean not allowing the application to leave the form (or change the active environment, to be more generic) until the user either corrects the reported error or cancels the update.
Removing a Table with Associated Rules from a Database
If you remove or delete a table from a database, all field-level and record-level rules bound to that table are deleted from the database. This is because the rules are stored in the .dbc file, and removing a table from the database breaks the link between the .dbf file and its .dbc file. However, stored procedures referenced by the removed or deleted rule are not deleted. They are not automatically removed, because they may be used by rules in other tables that remain in the database.