Neoview SQL Reference Manual (R2.5)

How Neoview SQL Supports Nullable Keys for Volatile Tables
• Allows nullable keys in primary key, HASH PARTITION BY, STORE BY, and unique
constraints.
• A null value is treated as the highest value for that column.
• A null value as equal to other null values and only one value is allowed for that column.
How Neoview SQL Selects Suitable Keys for Volatile Tables
Neoview SQL searches for the first suitable column in the list of columns of the table being
created. Once the column is located, the table is partitioned on it. The searched columns in the
table might be explicitly specified (as in a CREATE TABLE statement) or implicitly created (as
in a CREATE TABLE AS SELECT statement).
The suitable key column is selected only if no primary key, HASH PARTITION BY clause, or
STORE BY clause has been specified in the statement. If any of these clauses have been specified,
they are used to select the key columns.
Neoview SQL follows these guidelines to search for and select suitable keys:
• A suitable column can be a nullable column.
• Certain data types in Neoview SQL cannot be used as a partitioning key. Currently, this
includes any floating point columns (REAL, DOUBLE PRECISION, and FLOAT).
• Neoview SQL searches for a suitable column according to this predefined order:
— Numeric columns are chosen first, followed by fixed CHAR, DATETIME, INTERVAL,
and VARCHAR data types.
— Within numeric data types, the order is binary NUMERIC (LARGEINT, INTEGER,
SMALLINT), DECIMAL, and BIGNUM.
— An unsigned column is given preference over a signed column.
— A non-nullable column is given preference over a nullable column.
— If all data types are the same, the first column is selected.
• If a suitable column is not located, the volatile table becomes a non-partitioned table with
a system-defined SYSKEY as its primary key.
• If a suitable column is located, it becomes the partitioning key where the primary key is
suitable_column, SYSKEY. This causes the table to be partitioned while preventing the
duplicate key and null-to-non-null errors.
Table 2-1 shows the order of precedence, from low to high, of data types when Neoview SQL
searches for a suitable key. A data type appearing later has precedence over previously-appearing
data types. Data types that do not appear in Table 2-1 cannot be chosen as a key column.
Table 2-1 Precedence of Data Types During Suitable Key Searches
Precedence of Data Types (From Low to High)
VARCHAR
INTERVAL
DATETIME
CHAR(ACTER)
DECIMAL (signed, unsigned)
SMALLINT (signed, unsigned)
INTEGER (signed, unsigned)
LARGEINT (signed only)
80 SQL Statements