db2中常用的生成唯一主键的几种方式
一、generate_unique()
这个方法类似于Oracle中的sys_guid(),用于生成一个随机不重复的13个字节的字符串。常常用来作为表的主键使用。
create table customers (
custno char(13) for bit data,
custname char(16)
);
insert into customers (custno,custname) values (generate_unique(),'zhangsan'),(generate_unique(),'lisi');
二、sequence
下面看下db2中的序列,sequence,基本和Oracle一样,写法有些不同。
--sequence
create table seqtest(
id int,
name char(20)
);
create sequence my_seq start with 1 increment by 1 cache 100;
insert into seqtest(id ,name) values (nextval for my_seq,'zhangsan'),(nextval for my_seq,'lisi');
select * from seqtest;
三、identity
--identity
create table identitytest (
custno bigint not null generated always as identity (--generated by default as identity
start with 500,--qishiye
increment by 1,--zeng zhang liang
minvalue 500,--zuixiaozhi
maxvalue 50000,--zuidazhi
no cycle,--shifou xunhuan
no cache,--shifou huancun
no order--shifou youxu
),
custname varchar(30)
);
insert into identitytest (custname) values ('zhangsan'),('lisi');
select * from identitytest;