Oracle如何创建条件索引

首先讲述一个业务场景：

数据库商品表中有goods_id，goods_name，goods_price,status四个字段，goods_id是自增主键，status是状态，只有0，1两种可能，默认为1，goods_name是商品名称。要求状态为1 的商品名称不允许重复，状态为0的可以无限重复。

首先创建表：

-- Create table
create table TB_GOODS
(
goods_id    VARCHAR2(64) not null,
goods_name  VARCHAR2(256) not null,
goods_price VARCHAR2(64) not null,
status      VARCHAR2(1) not null
);

-- Create/Recreate primary, unique and foreign key constraints 
alter table TB_GOODS
add constraint PK_TB_GOODS primary key (GOODS_ID);

如何实现业务场景：

-- Create/Recreate indexes 
create unique index TB_GOODS_UNIQUE_INDEX on TB_GOODS (GOODS_NAME, DECODE(TO_NUMBER(STATUS),1,STATUS,GOODS_ID));

通过使用Oracle提供的decode函数实现：

如果状态为1则设定唯一索引为goods_name，status

如果状态不为1则设定唯一索引为goods_name，goods_id

这样，便可以随意插入状态为0的，商品名称相同的数据了。