Trafodion sequence与自增序列

来源：互联网发布：java里把大括号替换编辑：程序博客网时间：2024/06/04 19:01

Trafodion中创建SEQUENCE与Oracle类似，基本语法如下，

CREATE SEQUENCE seqname{START WITH bigint}{MINVALUE bigint}{MAXVALUE bigint}{INCREMENT BY bigint}{CACHE bigint}

下面创建一个最基本的Sequence，

SQL>create sequence seq1;--- SQL operation complete.SQL>showddl sequence seq1;CREATE SEQUENCE TRAFODION.SEABASE.SEQ1  START WITH 1 /* NEXT AVAILABLE VALUE 1 */  INCREMENT BY 1  MAXVALUE 9223372036854775806  MINVALUE 1  CACHE 25  NO CYCLE  LARGEINT;--- SQL operation complete.

那如何在表中添加自增序列呢？
在表中添加自增序列有两种方法，

GENERATE BY DEFAULT AS IDENTITY
GENERATE ALWAYS AS IDENTTIY

下面分别创建两个不同的表，

create table test1(aa  LARGEINT GENERATED BY DEFAULT AS IDENTITY,bb  VARCHAR(10));create table test2(aa  LARGEINT GENERATED ALWAYS AS IDENTITY,bb  VARCHAR(10));

查看两个表的定义，

SQL>showddl test1;CREATE TABLE TRAFODION.SEABASE.TEST1  (    AA                               LARGEINT GENERATED BY DEFAULT AS IDENTITY      (  START WITH 1  INCREMENT BY 1  MAXVALUE 9223372036854775806  MINVALUE 1       CACHE 25  NO CYCLE  LARGEINT  ) NOT NULL NOT DROPPABLE SERIALIZED  , BB                               VARCHAR(10) CHARACTER SET ISO88591 COLLATE      DEFAULT DEFAULT NULL SERIALIZED  );-- The following sequence is a system created sequence --CREATE SEQUENCE TRAFODION.SEABASE."_TRAFODION_SEABASE_TEST1_AA_" /* INTERNAL */  START WITH 1 /* NEXT AVAILABLE VALUE 1 */  INCREMENT BY 1  MAXVALUE 9223372036854775806  MINVALUE 1  CACHE 25  NO CYCLE  LARGEINT;--- SQL operation complete.SQL>showddl test2;CREATE TABLE TRAFODION.SEABASE.TEST2  (    AA                               LARGEINT GENERATED ALWAYS AS IDENTITY (      START WITH 1  INCREMENT BY 1  MAXVALUE 9223372036854775806  MINVALUE 1      CACHE 25  NO CYCLE  LARGEINT  ) NOT NULL NOT DROPPABLE SERIALIZED  , BB                               VARCHAR(10) CHARACTER SET ISO88591 COLLATE      DEFAULT DEFAULT NULL SERIALIZED  );-- The following sequence is a system created sequence --CREATE SEQUENCE TRAFODION.SEABASE."_TRAFODION_SEABASE_TEST2_AA_" /* INTERNAL */  START WITH 1 /* NEXT AVAILABLE VALUE 1 */  INCREMENT BY 1  MAXVALUE 9223372036854775806  MINVALUE 1  CACHE 25  NO CYCLE  LARGEINT;--- SQL operation complete.

分别往两个表插入数据，

SQL>insert into test1 values(1,'AAA');--- 1 row(s) inserted.SQL>insert into test2 values(1,'AAA');*** ERROR[3428] IDENTITY column AA defined as GENERATED ALWAYS cannot accept values specified by the user. [2016-11-24 17:41:38]

此时我们发现，GENERATE ALWAYS AS IDENTITY方式不能手动插入值，我们可以用下面方法代替，

SQL>insert into test2(bb) values('AAA');--- 1 row(s) inserted.SQL>select * from test2;AA                   BB-------------------- ----------                   1 AAA--- 1 row(s) selected.

1 0