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

1、需求

一张表上的字段A和B。
在B为100时(A,B)可以重复，其他时候(A,B)不能重复。
SQL> select * from tt1;

A                             B
-------------------- ----------
a                             1
b                             1
c                             1
a                           100
a                           100

2、实现方法

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

讨论后，认为在判断是否重复时，不能保证另外的会话也在做相同判断。这样的算法就可能导致多条相同记录插入。

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

如：
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));

3、实现过程

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>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26764/viewspace-528606/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26764/viewspace-528606/