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)
Date: Thu, 27 Oct 2005 09:01:40 -0700
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: Unique constraints on multiple fields with one nullable
In-reply-to: <200510262358.19729.msegel@segel.com>
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <4360F9E4.3050504@sun.com>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
References: <20051026223634.95863.qmail@web32507.mail.mud.yahoo.com>
 <43601DB4.1000605@sun.com> <200510262358.19729.msegel@segel.com>

Thanks, Michael. You are correct, Derby, like DB2, finesses this issue 
by not allowing nullable columns in unique constraints. I have closed 
this bug.

Cheers,
-Rick

Michael J. Segel wrote:

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