SET NULL, Set(“Null”)

Judicious absence is a weapon.
—Charles Reade

This command determines whether or not fields added or modified with CREATE TABLE and ALTER TABLE default to accepting nulls, and whether unspecified fields in INSERT-SQL are filled with null or empty values. The function returns the current setting.

Usage

SET NULL ON | OFF
cOnOrOff = SET( "NULL" )

When SET NULL is ON, all fields added in CREATE TABLE and ALTER TABLE and those respecified with ALTER TABLE allow nulls unless you include the NOT NULL clause. Conversely, when SET NULL is OFF, those fields don’t accept nulls unless you explicitly allow them by including the NULL clause in the definition. Boy, does that sound confusing, but it isn’t really. This command lets you set up a default, which applies unless you explicitly override it in the command.

The interaction of SET NULL with INSERT is really interesting. When you issue INSERT INTO a table, if you choose, you can provide values for only a subset of the fields. However, when you do that, the other fields still have to get created. Before Visual FoxPro, the value inserted into those fields was blank—that is, you could test ISBLANK() on the omitted fields and get .T. With SET NULL OFF, that’s still what you get. But when you SET NULL ON, those fields that accept nulls have them inserted.

Our advice (like that for many SET commands): Establish a standard for your entire application, set it once, and leave it that way. Toggling this on and off inside an application must be done with a lot of care.

Example

SET NULL OFF
CREATE TABLE Test1 (cfld C(3), nfld N(4) NULL)
* cfld doesn't accept nulls, but nfld does
SET NULL ON
CREATE TABLE Test2 (cfld C(3) NOT NULL, nfld N(4))
* same as above - no nulls for cfld, but nfld accepts them

* now let's put some data in
* NULL is still ON
INSERT INTO Test1 (cfld) VALUES ("abc")
SELECT Test1
? ISNULL(nfld)   && returns .t.
? ISBLANK(nfld)  && returns .f.
SET NULL OFF
INSERT INTO Test1 (cfld) VALUES ("def")
? ISNULL(nfld)   && returns .f.
? ISBLANK(nfld)  && returns .t.

See Also

Alter Table, Blank, Create Table, Insert-SQL, IsBlank(), IsNull(), Set