create table .... as注意事项

官方文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7002.htm#i2128916

AS subquery

Specify a subquery to determine the contents of the table. Therows returned by the subquery are inserted into the table upon itscreation.

For object tables, subquery can contain either oneexpression corresponding to the table type, or the number oftop-level attributes of the table type. Please refer to SELECT for more information.

If subquery returns the equivalentof part or all of an existing materialized view, then the databasemay rewrite the query to use the materialized view in place of oneor more tables specified in subquery.

See Also:

OracleData Warehousing Guide for more information onmaterialized views and query rewrite

 

Oracle Database derives datatypes and lengths from thesubquery. Oracle Database follows the following rules for integrityconstraints and other column and table attributes:

• Oracle Database automatically defines on columns in thenew table any NOT NULL constraints thatwere explicitly created on the corresponding columns of theselected table if the subquery selects the column rather than anexpression containing the column. If any rows violate theconstraint, then the database does not create the table and returnsan error.

•  

• NOT NULL constraints that wereimplicitly created by Oracle Database on columns of the selectedtable (for example, for primary keys) are not carried over to thenew table.

•  

• In addition, primary keys, unique keys, foreign keys,check constraints, partitioning criteria, indexes, and column default values are not carried over to thenew table. ---原来表的默认值是不会传递给新表的，需要在新表重新定义

•  

• If the selected table is partitioned, you can choosewhether the new table will be partitioned the same way, partitioneddifferently, or not partitioned. Partitioning is not carried overto the new table. Specify any desired partitioning as part of theCREATE TABLE statement before theAS subquery clause.

If all expressions in subquery are columns, ratherthan expressions, then you can omit the columns from the tabledefinition entirely. In this case, the names of the columns oftable are the same as the columns in subquery.

You can use subquery in combination with theTO_LOB function to convert the values in aLONG column in another table to LOB values in a columnof the table you are creating.

See Also:

• OracleDatabase Upgrade Guide for a discussion of why and whento copy LONG data to a LOB

• "Conversion Functions" for a description of how to use theTO_LOB function

• SELECT for more information on the order_by_clause

 

 

约束的描述如下：

文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm#SQLRF01111

 

constraint

Purpose

Use a constraint to define anintegrity constraint--a rule that restricts the valuesin a database. Oracle Database lets you create six types ofconstraints and lets you declare them in two ways.

The six types of integrity constraint are describedbriefly here and more fully in "Semantics": --oracle约束只有6种

• A NOT NULL constraintprohibits a database value from being null.

• A unique constraint prohibits multiple rowsfrom having the same value in the same column or combination ofcolumns but allows some values to be null.

• A primary key constraint combines aNOT NULL constraint and a uniqueconstraint in a single declaration. That is, it prohibits multiplerows from having the same value in the same column or combinationof columns and prohibits values from being null.

• A foreign key constraint requires values inone table to match values in another table.

• A check constraint requires a value in thedatabase to comply with a specified condition.

• A REF column by definition references anobject in another object type or in a relational table. A REFconstraint lets you further describe the relationshipbetween the REF column and the object itreferences.

You can define constraints syntactically in two ways:

• As part of the definition of an individual column or attribute.This is called inline specification.

• As part of the table definition. This is calledout-of-line specification.

NOT NULL constraints must be declaredinline. All other constraints can be declared either inline or outof line.