Oracle unique constraint with where clause

Question

I have an Oracle database table that I want to apply a unique constraint on. The issue is that I only want to apply the constraint if a column in that table is null,

ie. if a row does not have a deleted_date column, then apply the constraint, otherwise ignore it. This will allow for soft deleting records and ignoring constraints on them.

Any thoughts on how to do this?

Cheers, Mark

Answer 1

Just create a multi column constraint - the column you want to be unique plus the deletion date. All not deleted rows will have a unique value and the deletion date null. All deleted rows will be unique because of the deletion date (assuming it is a time stamp and the resolution is good enough to separate all deletions). If deleted rows cannot be separated by the deletion date, one could think about creating a new column and adding it to the constraint to uniquify the deletion date - but this would be quite an inelegant solution.

Answer 2

And if the resolution is not good enough, then you can create a unique function based index.

An example:

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.

This is the solution described by Daniel. If there is ever a possibility that two rows are deleted at the exact same time (I'm using only the date part here), this solution is not good enough:

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

In that case use a unique function based index:

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.

Regards, Rob.