Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
From: "Michael J. Segel" <msegel@segel.com>
To: "Derby Discussion" <derby-user@db.apache.org>
Subject: Re: Unique constraints on multiple fields with one nullable
Date: Wed, 26 Oct 2005 23:58:19 -0500
User-Agent: KMail/1.8
References: <20051026223634.95863.qmail@web32507.mail.mud.yahoo.com>
 <43601DB4.1000605@sun.com>
In-Reply-To: <43601DB4.1000605@sun.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200510262358.19729.msegel@segel.com>

On Wednesday 26 October 2005 19:22, Rick Hillegas wrote:

Sorry to top post...

Sigh.
Seems that some are quick to pull the trigger and call everything they see a 
bug!

This is not a bug. ;-)
Its a design issue.

I'm sure that this distinction is going to be lost on a couple of people, and 
it goes back to an earlier isssue about how each database handles 
constraints.  You can always e-mail me directly and take this offline.

Whomever designed how Derby handles constraints, Derby does not allow for 
NULLs in columns that have been identified in a constraint. Its actually in 
the reference manual. (The exercise of finding it is left to the 
student.... ;-)


Now if you want a simple work around, just create a unique index on the table.
Here's the code I just ran and it works:

CREATE TABLE foo 
	( id int NOT NULL,
	  name char(25)
	);
	
CREATE UNIQUE INDEX fidx ON foo (id, name);

Then I added the following rows:
	 INSERT INTO foo VALUES (1,'AAA');
	 INSERT INTO foo VALUES (2,'BBB');
	 INSERT INTO foo VALUES (3,'CCC');
	 INSERT INTO foo VALUES (4,'DDD');
	 INSERT INTO foo VALUES (5,'EEE');
	 INSERT INTO foo VALUES (1,'FFF');
 	INSERT INTO foo VALUES (6, NULL);

	INSERT INTO foo VALUES (1,'AAA'); -- THIS ROW FAILS!
	SELECT * FROM foo;

NOTE THE FOLLOWING:

Indexes are not the same as Constraints, however they can be used to achieve 
the same goal. 

Please remember, because you may not get the results that you expect, that 
doesn't mean that you actually have a bug.

If someone wants to consider how to redesign how contraints work, you have a 
couple of other considerations. For example, which takes precedence? SQL 
statements that manage the container, or SQL statements that manage the data?
(And that's a loaded question.... ;-)

HTH

-Mikey

PS. Again, what do I know?  Rumor has it my older siblings dropped me on my 
head when I was an infant. ;-)

> Hi Dan,
>
> I believe that the Sybase behavior is correct. I have logged bug 653 to
> track this issue.
>
> Regards,
> -Rick
>
> Dan Meany wrote:
> >I noticed that in Derby a unique constraint on two
> >columns A and B, with B nullable, will prevent
> >inserting two identical records that contain NULL in
> >B.
> >
> >This is different from some other databases such as
> >Sybase that do allow it (I assume where the null
> >records are not in stored as part of the unique
> >index).
> >
> >I noticed this while transfering data from Sybase to
> >Derby using Apache dbutils and getting rejected
> >duplicate records.

-- 
Michael Segel
Principal
MSCC
(312) 952-8175