函数目录和唯一索引的联合使用实现具条件判断功能的唯一约束
日期：2014-05-16 浏览次数：20557 次
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
