Using the NOT NULL Constraint

Use the NOT NULL keywords to require that a column receive a value during insert or update operations. If you place a NOT NULL constraint on a column (and no default value is specified), you must enter a value into this column when you insert a row or update that column in a row. If you do not enter a value, the database server returns an error, because no default value exists.

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

When you define 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.

You cannot specify NULL as the explicit default value for a column if you also specify the NOT NULL constraint.

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

The NOT NULL constraint is required for columns of the collection data types LIST, MULTISET, and SET. No other column constraints are allowed on a collection data type.


Copyright© 2019 HCL Technologies Limited