Share via

SET NULL Command

  • Article

Determines how null values are supported by the ALTER TABLE, CREATE TABLE and INSERT - SQL commands.

SET NULL ON | OFF

Parameters

  • ON
    Specifies that all columns in a table created with ALTER TABLE and CREATE TABLE will allow null values. You can override null value support for columns in the table by including the NOT NULL clause in the columns' definitions.

    Also, specifies that INSERT - SQL will attempt to insert null values into any columns not included in the INSERT - SQL VALUE clause. INSERT - SQL can successfully insert null values only into columns that allow null values.

    Note   If you add support for null values to one or more columns in a table, the limit on the number of columns for that table is reduced from 255 to 254.

  • OFF
    (Default) Specifies that all columns in a table created with ALTER TABLE and CREATE TABLE will not allow null values. You can designate null value support for columns in ALTER TABLE and CREATE TABLE by including the NULL clause in the columns' definitions.

    Also specifies that INSERT - SQL will insert blank values into any columns not included in the INSERT - SQL VALUE clause.

Remarks

SET NULL affects only how null values are supported by ALTER TABLE, CREATE TABLE and INSERT - SQL. Other commands are unaffected by SET NULL. SET NULL is scoped to the current data session.

Example

The following example demonstrates how SET NULL affects support for null values. The first table, employee, is created with SET NULL ON, so its fields support null values. REPLACE is used to place a null value in the cLastName field. The second table, staff, is created with SET NULL OFF, so its fields do not support null values. REPLACE is used to place zero in the cLastName field.

CLOSE DATABASES
SET NULL ON        && Fields will support null values
CREATE TABLE employee (cLastName C(20), ySalary Y(12,2))
APPEND BLANK       && Add a new blank record
REPLACE cLastName WITH .NULL.  && cLastName supports null values

SET NULL OFF       && Fields will not support null values
CREATE TABLE staff (cLastName C(20), ySalary Y(12,2))
APPEND BLANK       && Add a new blank record
REPLACE cLastName WITH 0   && Doesn't support null values

See Also

ALTER TABLE | CREATE TABLE | INSERT - SQL | ISNULL( ) | NVL( ) | SET DATASESSION