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: composite Unique constraint and null

Re: composite Unique constraint and null

From: amit poddar <amit.poddar_at_yale.edu>
Date: Tue, 18 Oct 2005 16:13:26 -0400
Message-ID: <43555766.5020207@yale.edu>


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
Received on Tue Oct 18 2005 - 15:15:36 CDT

Original text of this message

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