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-28
Managing Table Data
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 warnings 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.