Oracle unique constraint with where clause

Question

I have an Oracle database table in which I want to apply a unique constraint. The problem is that I want to apply the restriction only if the column in this table is null,

i.e. if there is no deleted_date column in the row, apply the restriction, otherwise ignore it. This will allow you to gently delete entries and ignore restrictions on them.

Any thoughts on how to do this?

Cheers, Mark

Answer 1

- , , . null. - ( , , ). , , , .

Answer 2

, , .

:

SQL> create table t (id,col,deleted_date)
  2  as
  3  select 1, 99, null from dual union all
  4  select 2, 99, date '2009-06-22' from dual
  5  /

Tabel is aangemaakt.

SQL> alter table t add constraint t_pk primary key (id)
  2  /

Tabel is gewijzigd.

SQL> alter table t add constraint t_uk1 unique (col,deleted_date)
  2  /

Tabel is gewijzigd.

, . , ( ), :

SQL> insert into t values (3, 99, date '2009-06-22')
  2  /
insert into t values (3, 99, date '2009-06-22')
*
FOUT in regel 1:
.ORA-00001: unique constraint (RWK.T_UK1) violated

, :

SQL> alter table t drop constraint t_uk1
  2  /

Tabel is gewijzigd.

SQL> create unique index i1 on t (nvl2(deleted_date,null,col))
  2  /

Index is aangemaakt.

SQL> insert into t values (3, 99, date '2009-06-22')
  2  /

1 rij is aangemaakt.

, .