Specifies that each field of a column must contain a non-null value.
ANSI Compliance
NOT NULL is ANSI SQL:2011 compliant.
Syntax
NOT NULL
Usage Notes
The NOT NULL specification for a column makes sure that all fields of that column contain a value. An error is returned if you attempt to insert row data that does not have a value for the column, or has a NULL for the column.
If a NOT NULL phrase is specified for a column in an ALTER TABLE statement, a DEFAULT or a WITH DEFAULT phrase must also be specified unless the table is empty. This makes sure that any empty field in that column is supplied with default values in compliance with the NOT NULL specification.
Teradata recommends specifying NOT NULL for all columns unless there is a valid reason to allow nulls.
If a column can contain nulls, understand what NULL means for the column, because NULL has multiple interpretations. Defining a non-null default value may be better than allowing nulls. If a non-null default value is specified for the column, specify NOT NULL for the column also.
If a column is nullable (even if no nulls are put in the column), every row has a presence bit for the column to indicate its nullability. This may cause an increase in row size if an additional presence byte is required to accommodate this presence bit. If the column must not have nulls, specifying NOT NULL eliminates the need for the presence bit and potentially saves a byte per row.
Specifying NOT NULL also allows for better optimization of queries because additional processing to handle nulls, or the potential of nulls in a column, can be avoided.
Example: NOT NULL Phrase
In the following table definition, the FullName column is defined as NOT NULL, thus ensuring that every row in the Members table has a value for FullName.
CREATE TABLE Members( FullName VARCHAR(42) NOT NULL, Status CHAR(6), Phone CHAR(10));