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

Using Default and Null Values
In NonStop SQL/MX, a null value is a marker that indicates that a column in a specified row has
no value. The null value is not treated as a normal data value. It serves only as a placeholder
necessary for certain relational operations. To an application interacting with a database, the null
value indicates “unknown” or “do not know.”
The DEFAULT and NULL clauses determine the value used when a column value is not supplied for
a row during data entry. The DEFAULT and NULL variations you can use and their effects on the
data allowed in columns are discussed next.
The DEFAULT and NULL clauses are independent of each other and must be specified separately.
The options for DEFAULT clause values apply in defining columns as follows:
• Use the NO DEFAULT clause in a column definition to specify that the column has no default
value, as you might when you want your application to explicitly supply values for the column.
The NO DEFAULT clause ensures that any inserted or updated row contains a value for the
column. The system does not allow the insert if the column value is omitted. The NO DEFAULT
restriction applies to inserts and updates made either directly to the table or through updateable
views. In particular, these guidelines apply:
◦ Declaring NO DEFAULT for a column requires the application to supply a value for the
column. The supplied value can be a null value.
◦ 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 be compatible with 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.
◦ For a character column, literal must be a string literal of no more than 240 characters
or the length of the column, whichever is less.
◦ For a numeric column, literal should be a numeric literal. NonStop SQL/MX issues
warning or error messages if the literal exceeds the defined length of the column. For
example:
– If the number of digits to the right of the decimal point exceeds the scale of the
column, NonStop SQL/MX issues a warning message that the value was truncated.
– 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.
94 Creating an SQL/MX Database