SQL 违反表的唯一约束条件（主键冲突）问题

问题

实际的项目中，采集上游数据到数据集市的贴源层 » 跑数据转换任务，将数据写入到我们的业务表，在跑转换任务的时候，时不时会报错主键冲突。转换里，通常写着insert update 语句，当向表执行插入和更新操作，时不时出现报错：违反表的唯一约束条件。

因为在实际业务中，中间层或者业务层表，通常情况下，是具有主键的（可能是单一主键，可能是复合主键）。

常见的两种情况

1）目标表里已经有了一条数据A，但是转换任务又往目标表里写入一条数据A，导致的报错；

2）目标表里没有数据，但是转换里，使用的关联条件产生了多条重复数据，导致的报错。

根据这两种情况对数据进行排查，看看是哪些数据重复了。

思路

1.根据报错的主键名称，查找该索引名是哪张表的主键名

1
2
3


select *
  from all_indexes a
 where a.index_name = '报错的主键名';

2.查看该表，查看该主键由哪些字段组成

3.对于第一种情况，根据这些字段，将目标表与转换里源表进行join，对索引字段进行count，查看是否有重复数据

例如这张表在转换里源表（from的表）为 ct.st_xxx，那么可以如下，查到重复的数据

1
2
3
4
5
6
7


select a.contract_no, a.stock_account, count(*)
  from sett.t_ref_contract a
  join ct.st_xxx b
    on a.contract_no = b.jys -- 关联字段
   and a.stock_account = b.zqzh
 group by a.contract_no, a.stock_account
having count(*) > 1;

然后，对比该数据，是否完全一致（因为数据可能出现只有主键字段是相同的，但是其他字段不同的情况）

1
2
3
4
5
6
7
8


select a.*
  from sett.t_ref_contract a
  join ct.st_xxx b
    on a.contract_no = b.jys -- 关联字段
   and a.stock_account = b.zqzh
 where a.contract_no = '查到的contract_no'
   and a.stock_account = '查到的stock_account'
 group by a.contract_no, a.stock_account;

3.1）若一致，则可将目标表里的数据删掉

3.2）若不一致，应咨询业务，它们是否有区别，根据业务沟通后的方案，将数据备份后，对其更改或删除等操作

4.对于第二种情况，则需要看转换里，它的insert 或者update 的逻辑，类似：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


insert table_a (
	col_1,
    col_2,
    ...
)
select col_a,
	   col_b,
	   ...
  from table_b t1
  join table_c t2
    on t1.col_a = t2.col_a
   and t1.col_b = t2.col_b;

然后对关联条件进行检查，看看是不是关联条件出现了 1:N N:N 的情况，如果是，则根据代码的逻辑，对该数据产生的原因进行定位，根据实际情况进行解决。