Re: composite Unique constraint and null

  • From: amit poddar <amit.poddar@xxxxxxxx>
  • To: dubey.sandeep@xxxxxxxxx
  • Date: Tue, 18 Oct 2005 16:13:26 -0400

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 -- //www.freelists.org/webpage/oracle-l




-- //www.freelists.org/webpage/oracle-l

Other related posts: