Oracle unique constraint with where clause

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.

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.