Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Check constraint

Re: Check constraint

From: Michael McMullen <ganstadba_at_hotmail.com>
Date: Wed, 27 Oct 2004 09:17:49 -0400
Message-ID: <BAY9-DAV94CnsdreDIO00003679@hotmail.com>


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
Received on Wed Oct 27 2004 - 08:15:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US