Neoview SQL Reference Manual (R2.4 SP2)

create volatile table t (a int) store by (a) partition by (a);
create volatile table t (a int unique);
Creating a Volatile Table With a Nullable Primary Key
This example creates a volatile table with a nullable primary key:
>>create volatile table t (a int, primary key(a));
--- SQL operation complete.
Only one unique null value is allowed:
>>insert into t values (null);
--- 1 row(s) inserted.
>>insert into t values (null);
*** ERROR[8102] The operation is prevented by a unique constraint.
--- 0 row(s) inserted.
Examples for Selecting Suitable Keys for Volatile Tables
These examples show the order by which Neoview SQL selects a suitable key based on the
precedence rules described in “How Neoview SQL Selects Suitable Keys for Volatile Tables”
(page 80):
• Selects column a as the primary and partitioning key:
Create volatile table t (a int);
• Selects column b because int has a higher precedence than char:
Create volatile table t (a char(10), b int);
• Selects column b because not null has precedence over nullable columns:
Create volatile table t (a int, b int not null);
• Selects column b because int has precedence over decimal:
Create volatile table t (a decimal(10), b int);
• Selects the first column, a, because both columns have the same data type:
Create volatile table t (a int not null, b int not null);
• Selects column b because char has precedence over date:
Create volatile table t (a date, b char(10));
• Selects column b because the real data type is not part of the columns to be looked at:
Create volatile table t (a real, b date);
• Does not select any column as the primary/partitioning key. Instead, Neoview SQL selects
SYSKEY as the primary key and the table becomes a non-partitioned table:
Create volatile table t (a real, b double precision not null);
Similar examples would be used for CREATE TABLE AS SELECT queries.
Considerations for CREATE TABLE LIKE
The CREATE TABLE LIKE statement does not create views, owner information, or privileges
for the new table based on the source table. Privileges associated with a new table created by
using the LIKE specification are defined as if the new table is created explicitly by the current
user.
For tables with IDENTITY columns, the target table inherits the IDENTITY property of a column
along with the corresponding sequence generating properties from the source table. Neither
CREATE TABLE Statement 81