RE: composite Unique constraint and null

  • From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
  • To: <dubey.sandeep@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 18 Oct 2005 16:03:22 -0400


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?


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sandeep Dubey
Sent: Tuesday, October 18, 2005 1:06 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: composite Unique constraint and null


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.


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!!



Other related posts: