composite Unique constraint and null 2005-10-18 - By amit poddar
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
|
|
