Oracle 条件索引

项目需求：

1.数据采用逻辑删除（非实际的物理删除），如将RecordStatus = 'A' (有效)变成RecordStatus = 'D' ；

2.当数据为有效数据（RecordStatus = 'A' ）时，几个关键字段不能同时与其它有效数据重复。

解决方法：

一开始将关键字段建立成主键。这个方法考虑欠缺就是，一旦做逻辑删除时，若已有的无效数据（RecordStatus = 'D' ）存在与当前操作的数据关键字段重复，数据库便插入报错（理应允许存在相同的无效数据）。所以此方法不可行。

最终采用方法如下：

建立唯一的条件索引：

将不能重复的关键字段作为组合索引(KeyColumns )，当RecordStatus='A' 时，才使用唯一索引，防止插入重复关键字段的数据。

CREATE UNIQUE INDEX INDEX_NAME ON TABLE (CASE "RecordStatus" WHEN 'A' THEN KeyColumns END );

举个栗子：

1.新建一张table

  CREATE TABLE "DUST"."CENCUS_TEST" 
   ( "NAME" VARCHAR2(10 BYTE), 
"AGE" NUMBER(3,0), 
"STATUS" VARCHAR2(1 BYTE)

   )

2.建立唯一的条件复合索引

当RECORDSTATUS=‘A’时，NAME 和AGE不能同时和其他有效的数据重复。

 CREATE UNIQUE INDEX "DUST"."TEST_INDEX" ON "DUST"."CENCUS_TEST" (CASE "RECORDSTATUS" WHEN 'A' THEN NAME ||','|| AGE END );

3.测试

1） INSERT INTO CENCUS_TEST VALUES ('CHUAN',1,'A');

2）再执行相同的insert语句。结果插入失败。unique constraint (DUST.TEST_INDEX) violated

3)  INSERT INTO CENCUS_TEST VALUES ('CHUAN',100,'A'); 结果：成功插入。

4)插入多条RECORDSTATUS=‘D’的重复数据。

INSERT INTO CENCUS_TEST VALUES ('CHUAN',1,'D');