Neoview SQL Reference Manual (R2.4)

predicate, and POSITION/REPLACE string function searches. See “Examples of CREATE
TABLE” (page 89).
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.
The include-option clauses are specified as:
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
You can create partitioned and non-partitioned tables. To create a non-partitioned table, specify
the NO PARTITION option with the CREATE TABLE command.
Considerations for CREATE SET TABLE
A SET table is like any other normal table with the additional property of discarding duplicate
rows. A SET table does not generate duplicate row errors or warnings upon insert. Duplicate
row means an entire row inclusive of all columns. If the row being inserted has only identical
key columns, a duplicate key error is generated, much like normal table behavior.
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.
For IDENTITY columns, the GENERATED BY DEFAULT AS IDENTITY option allows you to
supply the value or use the system-generated value. The GENERATED ALWAYS AS IDENTITY
option provides system-generated unique values only. If the values are user-provided, duplicates
could occur. In that case, the duplicate rows are automatically ignored and no errors occur.
Restrictions for CREATE SET TABLE
• CREATE SET TABLE is applicable only on base tables.
— not supported for index tables
• A row that contains default values for added columns cannot be treated as identical if the
inserting row contains the same default values in the corresponding columns.
76 SQL Statements