Re: composite Unique constraint and null

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