SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
N-8
Defining Columns That Allow or Prohibit Nulls
In deciding whether to allow nulls or use defaults, also note these points:

Null values are not the same as blanks. Two blanks can be compared and found
equal, while the equivalence of two null values is indeterminate.

Null values are not the same as zeros. Zeros can participate in arithmetic
operations, while null values are excluded from arithmetic.
Defining Columns That Allow or Prohibit Nulls
CREATE TABLE and ALTER TABLE define all the column attributes for columns of
tables. You use these statements to specify whether a new column allows null values.
A column allows null values unless the column definition includes the NOT NULL
clause or the column is part of the primary key of the table.
A null value is also the default value for a column unless the column definition includes
either the DEFAULT (excluding DEFAULT NULL) or the NO DEFAULT clause. (The
default value for a column is the value SQL inserts in a row when an INSERT
statement omits a value for a particular column or when a column is added to an
existing table.)
These sample column definitions allow or prohibit null values as indicated:
The SQL/MP Installation and Management Guide discusses defining columns with the
NULL and DEFAULT clauses in detail.
Determining Whether a Column Allows Nulls
To determine whether a column accepts null values, you can query the COLUMNS
catalog table or you can use INVOKE to list the table description in SQL format (the
default format from SQLCI) and check the column definitions. The COLUMNS table
contains descriptions of all columns of all tables registered in a catalog (as recorded in
the TABLES catalog table). The one-character NULLALLOWED column contains a Y if
a null value is allowed, and an N if not.
These examples illustrate how to display information through SQLCI about whether
columns allow or prohibit null values:

This example queries the value of the NULLALLOWED column in the COLUMNS
catalog table for the description of a particular column in a particular table. The
CA INTEGER Allows nulls, default null
CB INTEGER DEFAULT SYSTEM Allows nulls
CC INTEGER NO DEFAULT Allows nulls
CD INTEGER DEFAULT SYSTEM NOT NULL Prohibits nulls
CF INTEGER DEFAULT NULL Allows nulls, default null