ITPub博客

首页 > 数据库 > Oracle > [20151024]关于ctas与建立主键.txt

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

原创 Oracle 作者:lfree 时间:2015-10-24 21:24:36 0 删除 编辑
[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显示的字段决定,并且表后面要包括全部字段列表。
--还可以把约束放在里面(比如最后的例子)。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1816078/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6292504