oracle唯一性索引的用法,sql – Oracle：基于函数的索引选择唯一性

你需要类似的东西

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.