NOT NULL Phrase | Data Types and Literals | Teradata Vantage - NOT NULL Phrase - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
zsn1556242031050.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantageā„¢

Specifies that the fields of a column must contain a value; they cannot be null.

Syntax

NOT NULL

ANSI Compliance

NOT NULL is ANSI SQL:2011 compliant.

Usage Notes

The NOT NULL specification for a column ensures that all fields of that column will 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 ensures that any empty field in that column will be supplied with default values in compliance with the NOT NULL specification.

Recommendation: As a best practice, consider specifying NOT NULL for all columns unless there is a valid reason to allow nulls.

If a column should contain nulls, be sure you understand what NULL means for the column since NULL has many interpretations. In some cases, it is better to define a non-null default value rather than allow nulls. If a non-null default value is specified for the column, consider specifying NOT NULL for the column also.

If a column is nullable (even if no nulls are actually put in the column), every row will have 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 should 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 some queries since additional processing to handle nulls, or the potential of nulls in a column, can be avoided.

For more information on the advantages and disadvantages of using nulls, see Teradata Vantageā„¢ - Database Design, B035-1094.

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));