Using the NULL Constraint

Use the NULL keyword to specify that a column can store the NULL value for its data type. This implies that the column need not receive any value during insert or update operations. The NULL constraint is logically equivalent to omitting the NOT NULL constraint from the column definition.

The following example creates the newitems table. In newitems, the column descrip does not have a default value, but it allows NULL values.
CREATE TABLE newitems (
   newitem_num INTEGER,
   manucode CHAR(3) NOT NULL,
   promotype INTEGER,
   descrip CHAR(20) NULL);

In the example above, the columns newitem_num and promotype also allow NULL values implicitly, because no NOT NULL constraint is defined on them.

The CREATE TABLE statement fails with an error if you specify both a NOT NULL constraint and a NULL constraint on the same column.

You cannot specify both a NULL constraint and a PRIMARY KEY constraint on the same column, because when the CREATE TABLE statement defines a PRIMARY KEY constraint, the database server also silently creates a NOT NULL constraint on the same column, or on the same set of columns that make up the primary key.

The NULL constraint is not valid for columns of the collection data types LIST, MULTISET, and SET, nor for IDSSECURITYLABEL columns.


Copyright© 2019 HCL Technologies Limited