SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-26
Defining Columns
•
A date-time value cannot be multiplied by -1, but an INTERVAL value can be.
Negative date-time values are not valid.
These guidelines apply to arithmetic operations on date-time and INTERVAL data
types:
•
The result of subtracting two date-time values is an INTERVAL value.
•
The result of adding or subtracting INTERVAL values is an INTERVAL value.
•
The result of adding a positive INTERVAL value to a date-time value is a date-time
value with an increased value in a DATETIME field or fields. The result of
subtracting a positive INTERVAL value from a date-time value is a date-time value
with a decreased value in a DATETIME field or fields.
For instance, if you add INTERVAL (1) MONTH to a TIMESTAMP value, only the
MONTH field changes. The rest of the fields in the TIMESTAMP value remain the
same.
If the INTERVAL value for the field increases or decreases the value so that it
affects the value of another field, the other field changes accordingly. For instance,
if you add INTERVAL (5) MONTH to a MONTH field that is already 9, both the
MONTH field and the YEAR field change. If an arithmetic expression results in
invalid data, an SQL error is generated.
Using Default and Null Values
In SQL/MP, a null value is a marker that indicates that a column in a specified row has
no value. The null character is not treated as a normal data value; it serves strictly 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 following discussion describes the
DEFAULT and NULL variations you can use and their effects on the data allowed in
columns.
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 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 or update if the column value is omitted. The NO
DEFAULT restriction applies to inserts and updates made either directly to the
base table or through protection 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.