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-30
Managing Table Data
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 to 128 bytes in length if it is displayed with a SELECT statement
in MXCI. The heading name is an SQL identifier that can be delimited and its
characters must be from the ISO88591 character set.
If you specify a heading that is identical to the column name, INVOKE and SHOWDDL
do not display that heading. If you specify NO HEADING for the column, the
application uses the column name as the default.
UPSHIFT Attribute
The optional UPSHIFT attribute of the character types directs NonStop SQL/MX to
upshift characters before storing them in a column. For more information, see the
SQL/MX Reference Manual.
Defining Corresponding Columns in Different Tables
Data type cross-matching between tables is the responsibility of the database
administrator. Make sure that corresponding columns used in different tables are
defined with the same characteristics: data type, size, default values, and constraints.
For example, a part-number column defined in several tables should have the same
definition. Do not define one column as PIC X(4) and another as PIC 9(4). By using the
same column definition, you ensure that applications can perform join operations
during data retrieval or predicate comparisons.