Oracle：基于函数的索引选择唯一性 [英] Oracle: function based index selective uniqueness

问题描述

我必须保留历史记录，所以我使用的是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：基于函数的索引选择唯一性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！