Implications of Using NULL in Data Validation Rules (135753)

SUMMARY

Because Data Validations are expressions, NULL values inserted into fields and passed to Data Validation expressions behave in a consistent manner with that of other expressions. If the Data Validation for a field resolves to True (.T.) or False (.F.) with a NULL value, that logical result is accepted. In many cases, the expression will evaluate to NULL (For example, NULL > 1000). In this case, because the expression returns NULL, not True or False, it is rejected.

MORE INFORMATION

Step-by-Step Example

Create a program called Myprog.prg

Type in the following code segment:

   CREATE DATABASE mydata1
   CREATE TABLE mytable (lastname c(20) NULL, firstname c(20) NULL, ;
      city c(20) NULL CHECK city>"a")
   INSERT INTO mytable (lastname,firstname,city) ;
      VALUES ("Ansarti","Jim","San Jose")
   INSERT INTO mytable (lastname,firstname,city) ;
      VALUES ("Hayden","Rance",.NULL.)
   INSERT INTO mytable (lastname,firstname,city) ;
      VALUES ("Putnam","Phil","New Orleans")

Save the program.
In the Command window, type:
```
   DO Myprog.prg
```
The program executes, and the INSERT command with city equal to .NULL. generates the error message, "Field City Validation rule is violated."