db2主键自增、IDENTITY自增

五月天的尾巴

于 2024-06-28 09:12:59 发布

阅读量1.4k

点赞数 15

分类专栏：数据库文章标签： db2主键自增 IDENTITY自增

本文链接：https://blog.csdn.net/weixin_49114503/article/details/139509332

版权

数据库专栏收录该内容

38 篇文章 2 订阅

订阅专栏

db2设置主键自增

一、方式一：IDENTITY设置主键自增
二、方式二：Sequence(了解)
三、扩展
四、IDENTITY与Sequence的区别
五、Sequence的原理

每种数据都有自己独特的自增列的声明方式，如 Oracle 的 Sequence, SQL Server 的 Identity, MySQL 的 auto_increment, PostgreSQL 的 Sequence 或 Serial。和 PostgreSQL 类似，DB2 也提供两种自增列的声明方式，它们是 Sequence 和 Identity。而本文主要着墨于 DB2 的 Identity 字段，并讲述它与 Sequence 的某种联系，以及它对数据表的导入的影响。

一、方式一：IDENTITY设置主键自增

说明：IDENTITY的原理其实是一个匿名的Sequence，底层被包装成了Sequence在处理。所以想要详细了解IDENTITY原理的人也可以看一下下面的Sequence内容。

identity更多内容参见官网： https://www.ibm.com/docs/en/db2/11.5?topic=statements-create-table#sdx-synid_identity-options

DB2中有两种方式指定字段自增长

 1.GENERATED BY DEFAULT AS IDENTITY --插入数据时允许指定自增字段的值，只要不重复即可,数据库会自动设置下一个值
 2.GENERATED ALWAYS AS IDENTITY --不允许指定，只能由数据库自动分配并插入

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

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

1.1、语法一：GENERATED BY DEFAULT AS IDENTITY

CREATE TABLE t1(
    id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) primary key,
    name VARCHAR(400),
    age INTEGER
);
-- start with 1：id从1开始
-- increment by 1 ：每次自增1

参数简介如下：

start with：指定序列的起始值，默认情况下，对于升序的序列是当前指定数据类型的最小值，对于降序序列的是当前指定类型的最大值。
increment by：指定增长的值，默认值为1，正数表示此序列为增长升序的，负数表示此序列为降序的。
minvalue：最小值，若降序序列，no cycle的话，到此值的时候就不再生成序列值了；
no minvalue：对于升序序列来说，此值为start with的值，如果start with值未指定的话就是1. 对于降序序列来说，此值就是指定数据类型的最小值。
maxvalue：指定生产序列的最大值，
no maxvalue：对于升序序列来说，此值为指定数据类型的最大值；
对于降序序列来说，此值为start with值，若未指定start with值的话就是-1.
cycle：循环使用数据值，对于升序序列来说，当达到了最大值之后，下一个值将会是其最小值；
对于降序序列来说，达到最小之后，下一个序列值为其最大值。
no cycle：当达到序列的边界值之后，就不再产生序列值，默认选项。
cache：缓存序列值，表示每次应用此序列的时候，预先生产并存放在内存中的序列值。

其作用是有效的降低了写日志的I/O操作。
若在使用的过程中，出现系统错误的话，那么所有这些缓存值将会丢失。
最小值为2，默认为20

no cache：当指定此选项的时候，内存中不会存储任何序列值，无论出现什么异常现象都不会影响到此序列，
每次生存新的序列值，都会导致写日志的I/O操作。
order：按照请求的顺序生成值。
no order：不会按照请求的顺序生成值，默认情况。

1.1.1、使用

用法一：不指定id值，让数据库自增
insert into t1 (name,age) values('张三',11);
用法二：手动指定id值
insert into t1(id,name,age) values(2,'李四',12)

在这里插入图片描述

1.1.2、注意事项或坑

注意：若手动指定自增id值，当id自增到该值时，会报主键重复

案例描述： 以上述示例为参考，先插入一条数据，id自增为1；然后手动插入一条数据，指定id为2；此时再插入一条数据，不手动指定id使用数据库自增，此时会报主键重复

问题分析： 猜测自增的id,数据库内存中会存储当前最大值。手动插入的不会维护在内存中，导致继续按内存中的最大值进行自增，所以会有主键冲突问题。

案例示例

CREATE TABLE t1(
    id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
    name VARCHAR(400),
    age INTEGER,
    PRIMARY KEY (id)
);

insert into t1 (name,age) values('张三',11);
insert into t1(id,name,age) values(2,'李四',12);
insert into t1 (name,age) values('王五',13);

select * from t1;

在这里插入图片描述

报错内容：

[23505][-803] 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 "TEST.T1" from having duplicate values for the index key.. SQLCODE=-803, SQLSTATE=23505, DRIVER=4.23.42

解决方法

方法一：设置t1表id从指定值之后开始自增
ALTER TABLE t1 ALTER COLUMN id RESTART WITH 18;

方法二：重新执行一下插入语句即可。
原理：第一次插入失败后，自增值也会自增1，所以再执行一次就跳过那个重复值了。

1.2、语法二：GENERATED ALWAYS AS IDENTITY

CREATE TABLE t2(
    id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
    name VARCHAR(400),
    age INTEGER,
	PRIMARY KEY (id)
);
-- start with 1：id从1开始
-- increment by 1 ：每次自增1

使用示例

注意：insert语句中不能包含id字段，否则就报错。id只能由数据库自增
insert into t2 (name,age) values('张三',11);

加了id后就会报以下错误
在这里插入图片描述

二、方式二：Sequence(了解)

IBM 官方的相关文档 CREATE SEQUENCE statement

Sequence的方式比Identity要稍微麻烦一些，所以可以作为了解，主要还是使用IDENTITY。 IDENTITY的原理其实是一个匿名的Sequence，底层被包装成了Sequence在处理。

在 DB2 中声明一个 Sequence 与表的 Identity 字段的参数差不多，我们可以看作 Identity 是一个内联的 Sequence。先来看如何创建一个序列

语法：
 CREATE SEQUENCE <sequence-name>
          AS data-type                           默认 As Integer
          START WITH <numeric-constant>   
          INCREMENT BY <numeric-constant>               默认 INCREMENT BY 1
           MINVALUE <numeric-constant> | NO MINVALUE    默认 NO MINVALUE
           MAXVALUE <numeric-constant> | NO MAXVALUE    默认 NO MAXVALUE
           NO CYCLE | CYCLE                             默认 NO CYCLE
           CACHE <numeric-constant> | NO CACHE          默认 CACHE 20
           NO ORDER | ORDER                             默认 NO ORDER

示例：
CREATE SEQUENCE ORDER_SEQ
  AS data-type  BIGINT
  START WITH 1    
  INCREMENT BY 1 
  CACHE 24;

参数简介如下：

as datatype：指定数据类型，这里说明下，序列的数据类型只能为数值型，
如，smallint，integer，bigint，不带小数点的decimal类型。

2.1、insert时使用Sequence

insert into  t1(id,name,age) values(NEXT VALUE for ORDER_SEQ,'scott',30);

使用的话，比如 insert, update 记录时用它的 NEXT VALUE FOR ORDER_SEQ 值，我们也可以查询到它的下一个值

SELECT NEXT VALUE FOR ORDER_SEQ FROM SYSIBM.SYSDUMMY1;

注意：每调用一次next value 就会使用掉一个自增id值

2.2、查询下一个自增值

NEXTVAL FOR 序列 
或
NEXT VALUE FOR 序列
或
select  <序列名>.nextval from SYSIBM.SYSDUMMY1;

示例：
SELECT NEXTVAL FOR ORDER_SEQ FROM SYSIBM.SYSDUMMY1;
SELECT NEXT VALUE FOR ORDER_SEQ FROM SYSIBM.SYSDUMMY1;
select  ORDER_SEQ.nextval from SYSIBM.SYSDUMMY1;

2.3、查询上一个自增值或当前自增值

SELECT PREVIOUS　VALUE　FOR ORDER_SEQ FROM SYSIBM.SYSDUMMY1;

2.4、查看Sequence信息

SELECT * FROM sysibm.syssequences

三、扩展

3.1、修改列为自增长

当想将表中一列修改为自动增长时，可用下面命令：

语法：
alter table <table name> alter column <column name> set generated always as identity (start with 1,increment by 1);

示例：设置user表id字段为自增长
alter table user alter column id set generated always as identity (start with 1,increment by 1);

3.2、修改列自增长起始值

当修改表中一列自动增长的开始值时，可用下面的命令：

语法：
ALTER TABLE <talbe_name> ALTER COLUMN <column name> RESTART WITH <起始值>;

示例：设置user表id字段从18开始自增
ALTER TABLE user ALTER COLUMN id RESTART WITH 18;

3.3、修改SEQUENCE起始值

ALTER SEQUENCE 序列名  RESTART WITH 下一值

示例：
alter sequence order_seq restart with 10;

3.4、修改SEQUENCE序列信息

修改最大值：   ALTER SEQUENCE <sequence_name> MAX VALUE <numeric-constant> | NO MAXVALUE
修改最小值：   ALTER SEQUENCE <sequence_name> MIN VALUE <numeric-constant> | NO MINVALUE （此值需要比当前值小）
修改步长：     ALTER SEQUENCE <sequence_name> INCREMENT BY <numeric-constant>;
修改CACHE值：  ALTER SEQUENCE <sequence_name> CACHE <numeric-constant> | NO CACHE
修改循环属性:  ALTER SEQUENCE <sequence_name> <CYCLE | NO CYCLE>
修改排序属性： ALTER SEQUENCE <sequence_name> <ORDER | NO ORDER>
从新计数：     ALTER SEQUENCE <sequence_name> RESTART | RESTART WITH <numeric-constant>