SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-27
Defining Columns
°
Declaring NO DEFAULT NOT NULL for a column requires the application to
supply a value. The supplied value cannot be a null value.
•
Use the DEFAULT literal clause in a column definition when a literal default
value for the column is acceptable. The data type of the literal must match the data
type of the column, as follows:
°
Declaring a column with DEFAULT literal specifies using the default value
if no entry is made for the column. The column can contain a null value.
°
Declaring a column with DEFAULT literal NOT NULL specifies using the
default value if no entry is made for the column. The column cannot contain a
null value.
•
Use the DEFAULT SYSTEM clause when you want to use the SQL/MP default
value. The column data type determines the assigned system default value. The
default value for each type is as follows:
Numeric column Zero
CHARACTER column String of blanks
NATIONAL CHARACTER column String of blanks
VARCHAR column Zero-length string
NATIONAL CHARACTER VARYING column Zero-length string
Date-time column Zero
INTERVAL column Zero
Declaring a column with DEFAULT SYSTEM specifies initializing the column to the
system default value if no value is supplied for the column. The column can contain
a null value.
Declaring a column with DEFAULT SYSTEM NOT NULL specifies initializing the
column to the system default value if no value is supplied for the column. The
column cannot contain a null value.
•
Use the NOT NULL clause to specify that the column cannot contain null values. If
this clause is used separately without a DEFAULT clause, the user must supply a
value for this column.
•
Use the DEFAULT NULL clause to specify that the column takes on a null value if
no value is supplied for it.
•
You cannot use the DEFAULT NULL and NOT NULL clauses for the same column.
The use of a column as a key column or the partitioning of a table affects use of the
NULL clause as follows:
•
A column specified in the PRIMARY KEY or CLUSTERING KEY clause of a
CREATE TABLE statement cannot allow null values. Therefore, you should use the
NOT NULL clause to define primary key or clustering key columns. In the absence
of the NOT NULL clause, any columns defined in the PRIMARY KEY or
CLUSTERING KEY clause are implicitly set to NOT NULL, while all other columns
are implicitly set to allow nulls by default.