本文介绍了Oracle:基于函数的索引选择唯一性的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我必须保留历史记录,所以我使用的是is_deleted列,它可以有'Y'或'N'。但对于任何is_deleted'N'的实例,我应该为(a,b,c)复合列提供unwue条目。
I have to maintain history and so I am using is_deleted column which can have 'Y' or 'N'. But for any instance of is_deleted 'N' I should have uniwue entry for (a,b,c) composite columns.
当我尝试创建基于函数的唯一索引时我收到错误。
When I am tryin to create function based unique index I am getting error.
CREATE UNIQUE INDEX fn_unique_idx ON table1 (CASE WHEN is_deleted='N' then (id, name, type) end);
第1行的错误:
ORA-00907:缺少右括号
ERROR at line 1:ORA-00907: missing right parenthesis
请帮忙。
谢谢
推荐答案
你需要类似
CREATE UNIQUE INDEX fn_unique_idx
ON table1 (CASE WHEN is_deleted='N' THEN id ELSE null END,
CASE WHEN is_deleted='N' THEN name ELSE null END,
CASE WHEN is_deleted='N' THEN type ELSE null END);
行动中约束的一个例子
SQL> create table table1 (
2 id number,
3 name varchar2(10),
4 type varchar2(10),
5 is_deleted varchar2(1)
6 );
Table created.
SQL> CREATE UNIQUE INDEX fn_unique_idx
2 ON table1 (CASE WHEN is_deleted='N' THEN id ELSE null END,
3 CASE WHEN is_deleted='N' THEN name ELSE null END,
4 CASE WHEN is_deleted='N' THEN type ELSE null END);
Index created.
SQL> insert into table1 values( 1, 'Foo', 'Bar', 'N' );
1 row created.
SQL> insert into table1 values( 1, 'Foo', 'Bar', 'Y' );
1 row created.
SQL> insert into table1 values( 1, 'Foo', 'Bar', 'Y' );
1 row created.
SQL> insert into table1 values( 1, 'Foo', 'Bar', 'N' );
insert into table1 values( 1, 'Foo', 'Bar', 'N' )
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.FN_UNIQUE_IDX) violated
SQL> insert into table1 values( 1, 'Foo', 'Zee', 'N' );
1 row created.
这篇关于Oracle:基于函数的索引选择唯一性的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!