函数索引和唯一索引的联合使用实现具条件判断功能的唯一约束

 一表有字段A和B，若在B为100时(A,B)可以重复，其他时候(A,B)不可重复，如何实现？

SQL> select * from tt1;

A                             B

a                             1

b                             1

c                             1

a                           100

a                           100

 

实现方法

开始想到建立一触发器，在插入之前先判断本表中有没有相同记录，如何是B字段是100，则可以插入，如果不是，则退出。

但考虑后，认为在判断是否重复时，不能保证另外的会话也在做相同判断。这样的逻辑会导致多条相同记录插入。

这里使用一个技巧，使用具有函数索引和唯一索引联合实现该需求。

方式如下：

create table tt1(a varchar2(10),b number);

create Unique index ind_ab on tt1(decode(b,100,null,a),decode(b,100,null,b));

 

如果插入条件值重复，则会报错ORA-00001: unique constraint (SYS.IND_AB) violated

测试过程

SQL> create table tt1(a varchar2(10),b number);

Table created.

SQL> create Unique index ind_ab on tt1(decode(b,100,null,a),decode(b,100,null,b));

Index created.

SQL> insert into tt1 values('a',1);

insert into tt1 values('b',1);

1 row created.

SQL>

1 row created.

SQL> insert into tt1 values('c',1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into tt1 values('a',100);

commit;

1 row created.

SQL>

Commit complete.

SQL> insert into tt1 values('a',100);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tt1;

A                             B

-------------------- ----------

a                             1

 

b                             1

 

c                             1

 

a                           100

 

a                           100

SQL> insert into tt1 values('a',1);

commit;insert into tt1 values('a',1)

*

ERROR at line 1:

ORA-00001: unique constraint (SYS.IND_AB) violated

SQL>

 

墙外：http://mikixiyou.blogspot.com/2012/04/blog-post_10.html