[20151024]关于ctas与建立主键.txt
--前一阵子别人问的问题，就是ctas是否可以建立主键，对于这种情况平时不这么建立，我给看看文档。
--平时我建立测试表
create table t as select rownum id ,'test' name from dual connect by level<=10;
--要实现ctas同时建立主键，看看如下是否可行：
SCOTT@test01p> @ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test01p> create table t ( id number CONSTRAINTS pk_t primary key , name varchar2(20)) as select rownum,'test' from dual connect by level<=10 ;
create table t ( id number CONSTRAINTS pk_t primary key , name varchar2(20)) as select rownum,'test' from dual connect by level<=10
*
ERROR at line 1:
ORA-01773: may not specify column datatypes in this CREATE TABLE
SCOTT@test01p> create table t ( id number , name varchar2(20)) as select rownum,'test' from dual connect by level<=10 ;
create table t ( id number , name varchar2(20)) as select rownum,'test' from dual connect by level<=10
*
ERROR at line 1:
ORA-01773: may not specify column datatypes in this CREATE TABLE
--错误一样！
SCOTT@test01p> create table t ( id constraint pk_t primary key) as select rownum id,'test' name from dual ;
create table t ( id constraint pk_t primary key) as select rownum id,'test' name from dual
*
ERROR at line 1:
ORA-01730: invalid number of column names specified
SCOTT@test01p> create table t ( id primary key) as select rownum id,'test' name from dual ;
create table t ( id primary key) as select rownum id,'test' name from dual
*
ERROR at line 1:
ORA-01730: invalid number of column names specified
SCOTT@test01p> host oerr ora 1773
01773, 00000, "may not specify column datatypes in this CREATE TABLE"
// *Cause:
// *Action:
--从提示我的感觉字段id后面不能跟数据类型。
SCOTT@test01p> create table t ( id primary key) as select rownum id,'test' name from dual ;
create table t ( id primary key) as select rownum id,'test' name from dual
*
ERROR at line 1:
ORA-01730: invalid number of column names specified
SCOTT@test01p> create table t ( id primary key,name) as select rownum id,'test' name from dual ;
Table created.
--OK！
SCOTT@test01p> drop table t purge;
Table dropped.
SCOTT@test01p> create table t ( id constraints pk_t primary key,name) as select rownum id,'test' from dual ;
Table created.
SCOTT@test01p> drop table t purge;
Table dropped.
SCOTT@test01p> create table t ( id constraints pk_t primary key,name not null) as select rownum id,'test' from dual ;
Table created.
SCOTT@test01p> drop table t purge;
Table dropped.
SCOTT@test01p> create table t ( id ,name not null, constraints pk_t primary key (id,name)) as select rownum id,'test' from dual ;
Table created.
--总结：
--这种语法不常用。ctas 表后面的字段不能带数据类型，类型由select显示的字段决定，并且表后面要包括全部字段列表。
--还可以把约束放在里面(比如最后的例子)。