Supplement to the HP Neoview SQL Reference Manual (R2.3 SP2)

• 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 1-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 1-1 cannot be chosen as a key column.
Table 1-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)
Volatile Table Examples
Creating Nullable Constraints
These examples show the creation of nullable constraints (primary key, HASH PARTITION BY,
STORE BY, and unique) in a volatile table:
create volatile table t (a int, primary key(a));
create volatile table t (a int, primary key(a) droppable);
create volatile table t (a int) store by (a);
create volatile table t (a int, primary key(a)) partition by (a);
create volatile table t (a int) store by (a) partition by (a);
create volatile table t (a int unique);
Creating a Nullable Primary Keys
This example creates a volatile table with a nullable primary key:
>>create volatile table t (a int, primary key(a));
--- SQL operation complete.
>>showddl t, external;
CREATE VOLATILE TABLE T
(
A INT DEFAULT NULL
, CONSTRAINT T_829597611_5246 PRIMARY KEY (A ASC)
)
ATTRIBUTES ALIGNED FORMAT
HASH PARTITION
;
--- SQL operation complete.
Only one unique null value is allowed:
>>insert into t values (null);
10 Volatile Table Enhancements