SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

• Use the DEFAULT NULL clause to specify that the column takes on a null value if no value is
supplied for it.
• Use the NOT NULL clause to specify that the column cannot contain null values. If you omit
NOT NULL, nulls are allowed in the column. If you specify both NOT NULL and NO DEFAULT,
each row inserted in the table must include a value for the column.
◦ Use of the DROPPABLE clause with NOT NULL means that you can later drop the NOT
NULL constraint by using the ALTER TABLE statement. Dropping NOT NULL requires that
you know the name of the constraint, either by using the CONSTRAINT constraint-name
clause when the table is created or by using the SHOWDDL command to obtain the
constraint name.
◦ Use of the NOT DROPPABLE clause specifies that the NOT NULL constraint is permanent.
Less space is required to store a column if the column has a permanent NOT NULL
constraint, and updates and inserts are faster.
◦ If the NOT NULL constraint does not include the [NOT] DROPPABLE clause, NonStop
SQL/MX first checks the default value of the
NOT_NULL_CONSTRAINT_DROPPABLE_OPTION attribute in the CONTROL QUERY
DEFAULT statement. If a default value is not specified, NonStop SQL/MX next checks the
value of the NOT_NULL_CONSTRAINT_DROPPABLE_OPTION attribute in the
SYSTEM_DEFAULTS table. If the default attribute OFF (NOT DROPPABLE) is not specified
in either in the CONTROL QUERY DEFAULT statement or the SYSTEM_DEFAULTS table,
DROPPABLE is used. Use SHOWDDL to display the default used.
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. If you provide a null value, NonStop SQL/MX returns
an error message.
• A column used in nonunique index keys can contain null values.
• A column used as a unique key for a single-column index can contain null values, but you can
use only one row with a null value. Therefore, you might want to define this type of column
as NOT NULL. If you define the unique key through a unique constraint, the column must be
NOT NULL.
Unique multicolumn indexes can contain columns with null values. The same rule applies as
for single-column unique indexes; that is, the index can have at most one row of all null values
in the columns. Null values are treated as all other values and therefore cause duplication of
rows in the same way.
• For partitioning an index, a null value is considered greater than other values and equal to
other instances of the null value. Consequently, in ascending columns, a null value is sorted
after nonnull values, while in a descending column, a null value is sorted before nonnull values.
Use the NULL keyword in the FIRST KEY clause to specify a null value in a partitioning key.
Specifying Column Attributes
In addition to defaults, a column definition in CREATE TABLE can specify HEADING and UPSHIFT
attributes, which can be used by application programs.
HEADING Attribute
The HEADING attribute associates heading text with a column to enable applications to refer to
an alternate heading in place of the column name itself. A column can have a default heading up
Creating SQL/MX Tables 95