RE: composite Unique constraint and null

Sandeep,

It seems like your uniqueness rule backwards.

What you really want is uniqueness on the second column only. The id column just doesn't matter (at least given the example you gave us below), right?

Tom

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sandeep Dubey Sent: Tuesday, October 18, 2005 1:06 PM
To: oracle-l_at_freelists.org
Subject: composite Unique constraint and null

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:05:31 CDT