DB2关于标识列(自增列)的对比试验、使用示例

最新推荐文章于 2024-06-28 09:12:59 发布

JYWWABF

最新推荐文章于 2024-06-28 09:12:59 发布

阅读量1.9k

点赞数

分类专栏：数据库文章标签：数据库 db2数据库

原文链接：https://www.cnblogs.com/millen/archive/2011/10/11/2207550.html

版权

数据库专栏收录该内容

12 篇文章 0 订阅

订阅专栏

文章转载，原文地址：(https://www.cnblogs.com/millen/archive/2011/10/11/2207550.html)

试验环境：
建表t1、t2分别含有不同类型的标识列id，对比不同点

D:/>db2 connect to test user cwgladm using cwglpass

数据库连接信息

数据库服务器 = DB2/NT 8.2.9
SQL 授权标识 = CWGLADM
本地数据库别名 = TEST

D:/>db2 select char(TABSCHEMA,20),char(TABNAME,30),char(COLNAME,20),GENERATED from syscat.columns where IDENTITY='Y'

1                    2                              3                    GENERATED
-------------------- ------------------------------ -------------------- ---------

0 条记录已选择。

D:/>db2 create table t1(id bigint not null generated by default as identity ^
More? (start with 1,increment by 1) primary key, ^
More? name varchar(10))
DB20000I  SQL 命令成功完成。

D:/>db2 create table t2(id bigint not null generated always as identity ^
More? (start with 1,increment by 1) primary key, ^
More? name varchar(10))
DB20000I  SQL 命令成功完成。

D:/>db2 select char(TABSCHEMA,20),char(TABNAME,30),char(COLNAME,20),GENERATED from syscat.columns where IDENTITY='Y'

1                    2                              3                    GENERATED
-------------------- ------------------------------ -------------------- ---------
CWGLADM              T1                             ID                   D
CWGLADM              T2                             ID                   A

2 条记录已选择。

下面分别对这两种类型的标识列做试验，

对于generated by default：

D:/>
D:/>db2 insert into t1 (name) values('id1'),('id2') -- 可以自动生成标识列的值 注意此时标识列的下一个值为3
DB20000I  SQL 命令成功完成。

D:/>db2 select * from t1

ID                   NAME
-------------------- ----------
                   1 id1
                   2 id2

  2 条记录已选择。


D:/>db2 insert into t1 values(4,'id4'),(5,'id5')   -- 也可以手工输入标识列的值 
DB20000I  SQL 命令成功完成。

D:/>db2 select * from t1

ID                   NAME
-------------------- ----------
                   1 id1
                   2 id2
                   4 id4
                   5 id5

  4 条记录已选择。


D:/>db2 insert into t1 (name) values('id3')       -- **再次使用标识列自动生成值(id=3)，此时id将由3自动增长为4** 
DB20000I  SQL 命令成功完成。

D:/>db2 insert into t1 (name) values('test')      -- **再次使用标识列自动生成值时报错，因为主键已经有 id=4 的值了。**

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 "***.*****" from having duplicate values for the index key.. SQLCODE=-803, SQLSTATE=23505, DRIVER=3.57.82

DB21034E  该命令被当作 SQL语句来处理，因为它不是有效的“命令行处理器”命令。在 SQL 处理期间，它返回：
SQL0803N  INSERT 语句、UPDATE 语句或由 DELETE语句导致的外键更新中的一个或多个值无效，因为由 "1" 标识的主键、唯一约束或者唯一索引将表"CWGLADM.T1"的那些列限制为不能具有重复行。 SQLSTATE=23505


D:/>db2 insert into t1 (name) values('test')      -- **再次使用标识列自动生成值时报错，因为主键已经有 id=5 的值了**。

DB21034E  该命令被当作 SQL语句来处理，因为它不是有效的“命令行处理器”命令。在 SQL 处理期间，它返回：SQL0803N INSERT语句、UPDATE 语句或由 DELETE语句导致的外键更新中的一个或多个值无效，因为由 "1"标识的主键、唯一约束或者唯一索引将表
"CWGLADM.T1"的那些列限制为不能具有重复行。  SQLSTATE=23505

D:/>db2 insert into t1 (name) values('test')     -- 再次使用标识列自动生成值时成功，说明即使插入语句失败序列也会自增。
DB20000I  SQL 命令成功完成。

D:/>db2 insert into t1 (name) values('ttt')
DB20000I  SQL 命令成功完成。

D:/>db2 select * from t1

ID                   NAME
-------------------- ----------
                   1 id1
                   2 id2
                   4 id4
                   5 id5
                   3 id3
                   6 test
                   7 ttt

  7 条记录已选择。


D:/>db2 insert into t1 values(9,'id9'),(10,'id10')  
DB20000I  SQL 命令成功完成。

D:/>db2 select * from t1

ID                   NAME
-------------------- ----------
                   1 id1
                   2 id2
                   4 id4
                   5 id5
                   3 id3
                   6 test
                   7 ttt
                   9 id9
                  10 id10

  9 条记录已选择。


D:/>db2 insert into t1 (name) values('id8')
DB20000I  SQL 命令成功完成。

D:/>db2 alter table t1 alter id restart with 11 --可以手工重置标识列的起始值，以免自增的值与已插入的主键(id=9,10)冲突  
DB20000I  SQL 命令成功完成。

D:/>db2 insert into t1 (name) values('id11')
DB20000I  SQL 命令成功完成。

D:/>db2 insert into t1 (name) values('id12')
DB20000I  SQL 命令成功完成。

D:/>db2 select * from t1

ID                   NAME
-------------------- ----------
                   1 id1
                   2 id2
                   4 id4
                   5 id5
                   3 id3
                   6 test
                   7 ttt
                   9 id9
                  10 id10
                   8 id8
                  11 id11
                  12 id12

  12 条记录已选择。


D:/>

D:/>db2 update t1 set id=15 where id=12   -- 再次说明这种类型的标识列的值是可以修改的
DB20000I  SQL 命令成功完成。

再来看看generated always



D:/>
D:/>db2 insert into t2 (name) values('id1'),('id2')
DB20000I  SQL 命令成功完成。

D:/>db2 select * from t2

ID                   NAME
-------------------- ----------
                   1 id1
                   2 id2

  2 条记录已选择。


D:/>db2 insert into t2 values(4,'id4'),(5,'id5')   --  这种类型的标识列不能指定值，只能由系统生成。
DB21034E  该命令被当作 SQL 语句来处理，因为它不是有效的“命令行处理器”命令。在 SQL 处理期间，它返回：SQL0798N  不能对定义为 GENERATED ALWAYS 的列 "ID" 指定值。  SQLSTATE=428C9

D:/>db2 alter table t2 alter id restart with 5     --  可以重置起始值
DB20000I  SQL 命令成功完成。

D:/>db2 insert into t2(name) values('id5'),('id6')
DB20000I  SQL 命令成功完成。

D:/>db2 select * from t2

ID                   NAME
-------------------- ----------
                   1 id1
                   2 id2
                   5 id5
                   6 id6

  4 条记录已选择。


D:/>db2 alter table t2 alter id restart with 3
DB20000I  SQL 命令成功完成。

D:/>db2 insert into t2(name) values('id3'),('id4')
DB20000I  SQL 命令成功完成。

D:/>db2 insert into t2(name) values('id5')    -- 自增值与已有主键值冲突

> DB21034E  该命令被当作 SQL 语句来处理，因为它不是有效的“命令行处理器”命令。在 SQL 处理期间，它返回：SQL0803N 
> INSERT 语句、UPDATE 语句或由 DELETE 语句导致的外键更新中的一个或多个值无效，因为由
> "1"标识的主键、唯一约束或者唯一索引将表"CWGLADM.T2"的那些列限制为不能具有重复行。  SQLSTATE=23505

D:/>db2 select * from t2

ID                   NAME
-------------------- ----------
                   1 id1
                   2 id2
                   5 id5
                   6 id6
                   3 id3
                   4 id4

  6 条记录已选择。


D:/>db2 insert into t2(name) values('test')
DB21034E  该命令被当作 SQL
语句来处理，因为它不是有效的“命令行处理器”命令。在 SQL 处理期间，它返回：SQL0803N  INSERT 语句、UPDATE 语句或由 DELETE语句导致的外键更新中的一个或多个值无效，因为由 "1"标识的主键、唯一约束或者唯一索引将表"CWGLADM.T2"的那些列限制为不能具有重复行。  SQLSTATE=23505

D:/>db2 insert into t2(name) values('test')   -- 说明即使插入语句失败，标识列仍然自增
DB20000I  SQL 命令成功完成。

D:/>db2 select * from t2

ID                   NAME
-------------------- ----------
                   1 id1
                   2 id2
                   5 id5
                   6 id6
                   3 id3
                   4 id4
                   7 test

  7 条记录已选择。


D:/>db2 update t2 set id=9 where id=7          -- 再次确认这种类型标识列的值不可修改，只能有系统生成。
DB21034E  该命令被当作 SQL 语句来处理，因为它不是有效的“命令行处理器”命令。在 SQL 处理期间，它返回：SQL0798N  不能对定义为GENERATED ALWAYS 的列 "ID" 指定值。  SQLSTATE=428C9


D:/>

小结：

自增列有两种类型：generated by default、generated always

相同点：
1、即使插入语句失败，标识列仍然自增
2、都可以重置起始值，语句：alter table [table_name] alter [col_name] restart with [x]

不同点：
generated by default：可以修改，手工指定标识列的值。
generated always ：不可修改，只能由系统生成。

注意事项：

因上述标识列的特性在数据迁移时要特别小心处理！要仔细分析导入数据与目标库表里的标识列的当前值，以免主键冲突；如果改变导入数据的标识列，更要考虑导入表子表外键同步更新，所以要研究透彻再动手。
generated by default … ,该约束性弱，允许重置起始值，在导入数据时注意重新设置目标表的自增列的起始值，如自增列不连续，就麻烦了，要分段设置导数据。
generated always …约束性强，其值只允许系统根据其定义自动生成，在导入数据时，可以根据实际情况重置起始值后，再指定"忽略"选项，即modified by IDENTITYIGNORE。

补充

identityignore 选项还是由系统根据标识列的定义自动生成。应该用LOAD程序的identityoverride选项来导入文件里的内容，而不是有系统生成这些值，主要是外键关系的影响。

在新项目的数据库设计过程，强烈建议用sequence代替标识列！

JYWWABF

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
DB2关于标识列(自增列)的对比试验、使用示例

文章转载，原文地址：(https://www.cnblogs.com/millen/archive/2011/10/11/2207550.html)试验环境：建表t1、t2分别含有不同类型的标识列id，对比不同点D:/>db2 connect to test user cwgladm using cwglpass数据库连接信息数据库服务器 = DB2/NT 8.2.9SQL 授权标识 = CWGLADM本地数据库别名 = TEST.
复制链接

扫一扫