SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—523723-004
7-29
Managing Table Data
°
If the number of digits to the left of the decimal point exceeds the number
in the length (or length minus scale, if you specified scale for the column),
NonStop SQL/MX issues an error message.
°
For a datetime column, literal must be datetime literal with a precision that
matches the precision of the column.
°
For an INTERVAL column, literal must be an INTERVAL literal that has the
range of INTERVAL fields defined for the column.
•
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