[20151024]关于ctas与建立主键.txt

[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显示的字段决定，并且表后面要包括全部字段列表。
--还可以把约束放在里面(比如最后的例子)。