【ORACLE】条件唯一性索引的处理

背景

近期接到一个奇特的用户需求，有一张业务表，由于业务代码的问题，经常出现某一种类型下的脏数据，用户希望从数据库端能解决特定类型下的数据重复问题。

进一步沟通后确认，只有这种特定类型是不允许重复的。比如业务类型有A，B，C，....根据要求业务主键只需在类型A中不出现重复，在其他类型中不受限。

确认数据库结构，该表已根据类型字段分区。

实现操作手记

拿到需求首先想到的是本地索引，但是oracle不支持只在某个分区上建索引。

第二种方式是使用视图，通过视图过滤指定类型，再建唯一性索引，这种方法需要改造业务代码；

第三种方式是使用触发器，自建过程去判断，这个方式不太符合我们的开发规范，效率上也有顾虑；

查阅相关资料后，发现oracle支持条件唯一性索引，举例如下： 

drop table t_demo_a;
create table t_demo_a (id number,ctype varchar2(5),memo varchar(30))
partition by list (ctype)
(
partition p1 values('hello'),
partition p2 values('mille')
);

create unique index idx_demo_a on t_demo_a(case when ctype ='hello'  THEN ctype||memo ELSE null END);

上述语句的意思是创建一个唯一性索引，只有在字段ctype='hello'时，检查联合索引（ctype+memo）是否唯一。

以下是验证过程

select * from t_demo_a;
（1）insert into t_demo_a values(1,'mille','aaa');
（2）insert into t_demo_a values(2,'hello','aaa');
（3）insert into t_demo_a values(3,'hello','aaa');
（4）insert into t_demo_a values(4,'mille','aaa');

当插入（3）时，报违反唯一性索引；

当插入（4）时，数据插入正常。

最终结果是符合预期的。

经过验证，这种方式，在分区表上不能创建本地索引。