Neoview SQL Reference Manual (R2.5)

specified in the FOREIGN KEY clause. For information about ref-spec, see REFERENCES
ref-spec NOT ENFORCED.
NOT CASESPECIFIC
is a column constraint that specifies that the column contains strings that are not case specific.
The default is CASESPECIFIC. Comparison between two values is done in a case insensitive
way only if both are case insensitive. This applies to comparison in a binary predicate, LIKE
predicate, and POSITION/REPLACE string function searches. See “Examples of CREATE
TABLE” (page 93).
LIKE source-table [include-option]...
directs Neoview SQL to create a table like the existing table, source-table, omitting
constraints (with the exception of the NOT NULL and PRIMARY KEY constraints), and
partitions unless include-option clauses are specified.
source-table
is the ANSI logical name for the existing table and must be unique among names of tables,
views, and procedures within its schema.
include-option
WITH CONSTRAINTS
directs Neoview SQL to use constraints from source-table. Constraint names for
table are randomly generated unique names.
When you perform a CREATE TABLE LIKE, whether or not you include the WITH
CONSTRAINTS clause, the target table will have all the NOT NULL column constraints
that exist for the source table with different constraint names.
WITH PARTITIONS
directs Neoview SQL to use partition definitions from source-table. Each new
table partition resides on the same volume as its original source-table counterpart.
The new table partitions do not inherit partition names from the original table. Instead,
Neoview SQL generates new names based on the physical file location.
If you specify the LIKE clause and the PARTITION file-option, you cannot specify
WITH PARTITIONS.
Considerations for CREATE TABLE
The following subsections provide considerations for various CREATE TABLE options:
• “Considerations for CREATE SET TABLE” (page 78)
• “Considerations for CREATE VOLATILE TABLE” (page 79)
• “Considerations for CREATE TABLE LIKE” (page 81)
• “Considerations for LOAD IF EXISTS and NO LOAD options of CREATE TABLE AS”
(page 84)
• “Considerations for CREATE TABLE AS” (page 84)
• “Calculating Row Size” (page 87)
• “Generating Values For an IDENTITY Column” (page 88)
• “Considerations for DISK POOL” (page 92)
Considerations for CREATE SET TABLE
The CREATE SET TABLE statement allows:
• Insert-select queries to continue processing without interruption in the event of duplicate
rows in the source table.
• Data loading tools to blindly load a table without worrying about duplicate rows in the
source data.
• IDENTITY columns are supported.
78 SQL Statements