Re: composite Unique constraint and null

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_at_yale.edu> 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: 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