The following general rules apply to table definitions copied using an AS clause in CREATE TABLE.
- When you use a subquery as your source definition, if you do not explicitly specify a column set to define the primary index, primary key, or a unique constraint for the target table, then the first column defined for the table is selected to be its nonunique primary index by default (see About Primary-Indexing, Row-Partitioning, Column-Partitioning, NoPI Tables, and Secondary Indexes).
- When you specify a subquery as your source definition, a column that maps to an expression assumes the data type of the expression result.
- When you specify a subquery as your source definition and also specify the WITH DATA AND STATISTICS option, a special set of rules applies. See Specific Rules For AS … WITH DATA AND STATISTICS That Uses A Subquery To Define The Source Table Column And Index Set.
- When you specify an existing table (not as defined by a derived table subquery) as the source table definition in the AS clause, then the new table always assumes the following table-level characteristics of the source table.
- Column structures, including columns with UDT, Period, ARRAY, and VARRAY types.
- Fallback options
- Journaling options (except when the new table is a global temporary or volatile table, in which case permanent journaling is not copied).
- All defined indexes except hash and join indexes.
Note that if new indexes are defined for the target table, then that table does not assume any existing indexes.
- When you specify an existing table (that is not specified as a subquery) as the source table definition in the AS clause and also specify the WITH DATA AND STATISTICS option, a special set of rules applies.
- If a partitioned primary index definition is copied to a new target table and no index definitions are specified for the target table, then that table is partitioned in the same way as the source table.
- If a partitioned primary index definition is copied to a new target table, no index definitions are specified, and the CREATE TABLE statement for the target table renames the partitioning columns, then those columns are also renamed in the partitioning expression for the target table.
- Column definitions can contain any of the following attributes.
- Column names
- Default values
- NOT NULL constraints
- CHECK constraints
- UNIQUE constraints
- Columns in the target table are in the same left-to-right order as they are defined in the source table or source subquery select list.