Trafodion sequence与自增序列

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.

Trafodion关于sequence的详细语法，如添加sequence、修改sequence、删除sequence，可以查看官方文档，
CREATE SEQUENCE
ALTER SEQUENCE
DROP SEQUENCE

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

• 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.