DB2中IDENTITY COLUMN测试

文章主要目地是讲述DB2中含有IDENTITY COLUMN的表的创建，以及其行为表现、常见报错

创建表T1, ID列为IDENTITY列，从1开始，每次加1，最大值为3

$ db2 "create table t1(name char(20), id int not null generated always as identity (START WITH 1 INCREMENT BY 1 MAXVALUE 3) primary key)"
DB20000I  The SQL command completed successfully.

$ db2 "insert into t1 values('miao', DEFAULT)"
DB20000I  The SQL command completed successfully.

$ db2 "insert into t1 values('qing', DEFAULT)"
DB20000I  The SQL command completed successfully.

$ db2 "insert into t1 values('song', DEFAULT)"
DB20000I  The SQL command completed successfully.

如果已经达到最大值了，再次插入就会出现报错SQL0359N，这时可修改maxvalue，第一次修改为5；第二次修改为no maxvalue，意思是没有最大值，则最大值为int类型的最大值2147483647

$ db2 "insert into t1 values('ddd', DEFAULT)"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0359N  The range of values for the identity column or sequence is
exhausted.  SQLSTATE=23522

$ db2 "select * from t1"

NAME                 ID
-------------------- -----------
miao                           1
qing                           2
song                           3

  3 record(s) selected.
  
$ db2 "alter table t1 alter column id set maxvalue 5"  
DB20000I  The SQL command completed successfully.

$ db2 "alter table t1 alter column id set no maxvalue"
DB20000I  The SQL command completed successfully.

$ db2look -d test -a -e -t t1

CONNECT TO TEST;

------------------------------------------------
-- DDL Statements for Table "MIAOQINGSONG"."T1"
------------------------------------------------

CREATE TABLE "MIAOQINGSONG"."T1"  (
                  "NAME" CHAR(20 OCTETS) ,
                  "ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
                    START WITH +1
                    INCREMENT BY +1
                    MINVALUE +1
                    MAXVALUE +2147483647
                    NO CYCLE
                    CACHE 20
                    NO ORDER ) )
                 IN "USERSPACE1"
                 ORGANIZE BY ROW;

-- DDL Statements for Primary Key on Table "MIAOQINGSONG"."T1"

ALTER TABLE "MIAOQINGSONG"."T1"
        ADD PRIMARY KEY
                ("ID");


ALTER TABLE "MIAOQINGSONG"."T1" ALTER COLUMN "ID" RESTART WITH 4;

可以重新设置下一次要插入的值从哪里开始：

$ db2 "ALTER TABLE T1 ALTER COLUMN ID RESTART WITH 110"
DB20000I  The SQL command completed successfully.

$ db2 "insert into t1 values('ddd',default)"
DB20000I  The SQL command completed successfully.

$ db2 "select * from t1"

NAME                 ID
-------------------- -----------
miao                           1
qing                           2
song                           3
ddd                          110 <---

  4 record(s) selected.
  
$ db2 "ALTER TABLE T1 ALTER COLUMN ID RESTART WITH 110"  <--再次修改为110，则第一次插入操作会报错，因为该列定义为了主健，不允许重复（如果去掉主健，是没问题的）
DB20000I  The SQL command completed successfully.

$ db2 "insert into t1 values('eee',default)"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0803N  One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "MIAOQINGSONG.T1" from having duplicate values for the index key.
SQLSTATE=23505 

$ db2 "insert into t1 values('eee',default)" <--第二次插入没有问题
DB20000I  The SQL command completed successfully.

$ db2 "select * from t1"

NAME                 ID
-------------------- -----------
miao                           1
qing                           2
song                           3
ddd                          110
eee                          111

  5 record(s) selected.