Amit - I thought of this but this may fail. Here my columns are varchar2. Values like ('A-X' , 'A') and ('A' , '-XA') will fail.
I figured out another way of doing it. I have synthetic primary key on the table.
I created index on (col1, nvl(col2, pk_column))
Thanks for all replies.
Regards
Sandeep
On 10/18/05, amit poddar <amit.poddar@(protected)> wrote: > How about this > > SQL> create table test (id1 number(10), id2 number(10)); > > Table created. > > SQL> create unique index test_ind on test (case when id2 is not null > then to_char(id1)||'-'||to_char(id2) end) > 2 / > > Index created. > > SQL> insert into test values (1,null); > > 1 row created. > > SQL> insert into test values (1,null); > > 1 row created. > > SQL> insert into test values (1,1); > > 1 row created. > > SQL> insert into test values (1,1); > insert into test values (1,1) > * > ERROR at line 1: > ORA-00001 (See ORA-00001.ora-code.com): unique constraint (APPS.TEST_IND) violated > > > Sandeep Dubey wrote: > > >Hi, > > > >I want to enforce a business rule on two columns such that col1, col2 > >should be unique. However for a given value of col1 nulls should be > >allowed in col2. I can not implement that using a simple composite > >unique constraint. > > > >Eg. > > > >create table foo(id number, name varchar2(10)); > > > >insert into foo values(1,1); > >insert into foo values(1,1); -- should not be allowed > > > >But following should be allowed > > > >insert into foo values(1,null); > >insert into foo values(1,null); -- should be allowed > > > >Any ideas!! > > > >Thanks > > > > > >Sandeep > >Sandeep > >-- > >http://www.freelists.org/webpage/oracle-l > > > > > > > > -- http://www.freelists.org/webpage/oracle-l
