db2中常用的生成唯一主键的几种方式

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;