oracle 序列值导致的主键冲突问题

oracle 序列值导致的主键冲突问题

 

背景：操作中我是先导出了数据库的结构（包括序列），再导入数据（数据来自另一个库）。这导致了部分表相应的序列值（比如表YK_YKLB，用序列SEQ_YK_YKLB的值来作为表的主键值）小于主键的最大值，这样插入数据时会报错主键冲突。

解决方法：希望将有问题的序列值增大到表的主键最大值+50

（当然啦有一种更简单粗暴的方法，就是把所有的序列值统统往上加800 或者更大，但这种方法未必能解决所有问题，可能有漏网之鱼。下面是一种更精准的方法，直接比较表主键最大值和相应序列值）

第一步：找出主键为单列的表（多列构成联合主键的情况，插入数据时不会仅从序列取值作为主键，不会出现问题）和主键所在的列

SELECT max(COLUMN_NAME) col,max(TABLE_NAME) t
FROM user_cons_columns
WHERE constraint_name IN (select constraint_name from user_constraints WHERE constraint_type ='P') --筛选主键约束
GROUP BY CONSTRAINT_NAME
HAVING count(1) = 1

 

第二步：找出这些表的主键的最大值、表的序列的当前值

新建MAXVAL_SEQVAL_TABLE表，这个表包含3各字段：MAXVAL（表的主键的最大值），SEQVAL（表的序列的当前值），TABLENAME（表名）

▲执行此语句 建表语句：

create table MAXVAL_SEQVAL_TABLE(MAXVAL varchar2(100),SEQVAL varchar2(100),TABLENAME varchar2(100));

 

将各个表的主键最大值、序列值、表名找出来，插入MAXVAL_SEQVAL_TABLE表。

▲执行此语句 查询语句，拼接sql

select max(COLUMN_NAME) col, max(TABLE_NAME) t, 'INSERT INTO maxval_seqval_table SELECT (select max(' || max(COLUMN_NAME)|| ') from ' || max(TABLE_NAME)|| ') maxVal,SEQ_' || max(TABLE_NAME)|| '.nextval AS seqval, ''' || max(TABLE_NAME)|| ''' AS t FROM dual;'
from user_cons_columns
where constraint_name in (select constraint_name
from user_constraints
where constraint_type = 'P')--筛选主键约束
and ('SEQ_' || TABLE_NAME) in (select sequence_name
from USER_SEQUENCES)
group by CONSTRAINT_NAME
having count(1) = 1;

 

结果是这样的：

 

▲执行此语句 将查询结果的sql语句复制出来，执行

就将所有有用的数据插入到了MAXVAL_SEQVAL_TABLE表。表中的数据如下：

 

第三步：将序列（假定序列都是以规范的方式：SEQ_表名命名的）值改为相应表的主键最大值+50

▲执行此语句 新建一列MAXVAL_add，存储主键值+50的值

alter table MAXVAL_SEQVAL_TABLE add MAXVAL_add number;
update MAXVAL_SEQVAL_TABLE set MAXVAL_add = TO_NUMBER(MAXVAL) + 50;

 

有了MAXVAL_SEQVAL_TABLE表，下面就很好办了。删除、重建序列的语句拼接一下

▲执行此语句 将下面查询结果复制到sql窗口或控制台中执行

SELECT
'DROP SEQUENCE SEQ_'||TABLENAME||';CREATE SEQUENCE SEQ_'||TABLENAME||' INCREMENT BY 1 MINVALUE 1 MAXVALUE 9999999999 NOCYCLE CACHE 20 NOORDER start WITH '||maxVal_Add||';'
FROM maxval_seqval_table WHERE TO_NUMBER(MAXVAL) >= TO_NUMBER(SEQVAL)

 

注意：这里的条件一定要是TO_NUMBER(MAXVAL) >= TO_NUMBER(SEQVAL)，不能少了TO_NUMBER！！否则就是字符串的比较，没有意义

查询结果复制出来，执行

执行完后，可将MAXVAL_SEQVAL_TABLE表删除，因为表中数据只能反映上次静态收集的信息。

所有有问题的序列值就都更改啦！再也不会有插入数据主键冲突的问题！

后来想了一下，这个方法也有一些缺陷，比如序列的命名是否规范。如果序列和表不是按照规范命名的，那就白搭了。。序列是怎么命名的，还要研究一下orm框架，比如本项目用的是hibernate。

注意：此方法最好在业务停止或很少的时候执行，因为涉及到删除与重建序列，可能与正在进行的业务冲突

SELECT *
FROM MAXVAL_SEQVAL_TABLE
WHERE seqval < MAXVAL;