Re: Check constraint

I tried this and it worked.

ODMDB SQL> create table test(id number,status varchar2(8));

Table created.

ODMDB SQL> create unique index foo on test (case when status = 'Current' then id else null end);

Index created.

ODMDB SQL> analyze table test compute statistics;

Table analyzed.

ODMDB SQL> insert into test values (1, 'Current');

1 row created.

ODMDB SQL> insert into test values (1,'Junk');

1 row created.

ODMDB SQL> insert into test values (1, 'Current'); insert into test values (1, 'Current')
*
ERROR at line 1:
ORA-00001: unique constraint (R_MONITOR.FOO) violated

--
http://www.freelists.org/webpage/oracle-l