NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
N-7
Using Null Values Versus Default Values
You cannot store a null value in a column, either with INSERT or UPDATE, unless the
column was declared to allow null values when it was created.
Any row of a column that allows null values can be empty. In SQL, a column that allows
null values has two extra bytes associated with it in each row. A -1 stored in those two
bytes indicates that the column has a null value for that row; a 0 indicates a null value.
Using Null Values Versus Default Values
Various scenarios exist in which a row in a table might contain no value for a specific
column. For example:
•
A database of telemarketing contacts might have AGE fields empty if contacts did
not give their age.
•
An order record might have a DATE_SHIPPED column empty until the order is
actually shipped.
•
An employee record for an international employee might not have a social security
number.
You allow null values in a column when you want to convey that a value in the column
is either unknown (such as the age of a telemarketing contact) or not applicable (such as
the social security number of an international employee).
In deciding whether to allow nulls or use defaults, also note the following 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:
CA INTEGER Allows nulls, default
null
CB INTEGER DEFAULT SYSTEM Allows nulls