How to: Create Record Validation Rules

You use record-level validation rules when you want to control the type of information a user can enter in a database table record. You can also specify a custom message to display for invalid values instead of the default error message.

Note

Make sure that field validation rules do not conflict semantically with record validation rules. Visual FoxPro does not compare the field-level and record-level expressions for consistency.

To create a record validation rule

  1. Open the database containing the table.

  2. Open the table in the Table Designer.

  3. In the Table Designer, click the Table tab.

  4. In the Rule box from the Record validation area, type the validation expression you want. To build an expression, click the ellipsis (...) button.

  5. When you are finished, click OK.

  6. To display a custom error message, in the Message box, type the custom error message or expression you want to display for invalid values. To build an expression, click the ellipsis (...) button.

    Note

    Make sure to enclose message text in quotation marks ("").

  7. When you are finished, click OK.

For more information, see Table Tab, Table Designer.

To create a record validation rule programmatically

  • When creating the table using the SQL CREATE TABLE command, include the CHECK clause. To include custom error message text, include the ERROR clause.

    -OR-

  • To edit an existing table, open the table with the USE command and then use the SQL ALTER TABLE command with the CHECK or SET CHECK clause. To include custom error message text, include the ERROR clause.

For more information, see CREATE TABLE - SQL Command and ALTER TABLE - SQL Command.

Note

You cannot use the DBSETPROP( ) function to set record validation rules and error text; however, you can retrieve them using the DBGETPROP( ) function and the RuleExpression and RuleText table properties. For more information, see DBSETPROP( ) Function.

For example, suppose you want to make sure that new employees in an employee table are equal to or greater than 18 years old. The following code adds a record validation rule requiring that the hire date in the Hire_Date column is equal to or greater than the birth date plus 18 years using the SET CHECK clause. The ERROR clause specifies a custom error message:

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 the custom error message you defined and does not update the record.

See Also

Tasks

How to: Create Field Validation Rules
How to: Open Databases
How to: Edit Table Structure
How to: Remove Validation Rules

Concepts

Field and Record Validation Rules

Other Resources

Working with Fields
Working with Tables (Visual FoxPro)
Working with Validation Rules