db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: unique columns must be nullable ?
Date Wed, 13 Dec 2006 00:28:55 GMT


Craig L Russell wrote:
> On Dec 12, 2006, at 2:12 PM, Daniel Noll wrote:
> 
>> Mike Matrigali wrote:
>>
>>> Out of curiousity what behavior does Hibernate expect when 2 nulls  
>>> are inserted into a "unique" nullable column?
>>
>>
>> That's my issue with unique nullable columns too.  If it were to be  
>> truly unique you could only ever have one null in that column for  the 
>> entire table, which would all but make it pointless to set it  to null.
I was not making a value judgement, just interested in what hibernate
expected.  I've heard arguments for both sides.  As discussed in the
referenced threads the reality is that the current underlying mechanism
for implementing unique constraints are the underlying btree store 
indexes which unlike SQL compare NULL's as equal - which is why derby
does not currently implement the optional SQL constraint on nullable 
columns.  The other parts of SQL complient null handling happens outside
of the storage system and complies with the described null comparison
below.

> 
> 
> IIUC, a null value means that the value is unknown. So putting two  
> nulls into the same column with a unique constraint makes sense to  me. 
> SQL is not Java, and "NULL" is not something that you can compare  in 
> SQL like you can in Java...
> 
> And in the case of mapping to an optional 1-1 relationship, any  number 
> of rows can be associated with "none of the above" without any  problems.
> 
> Consider your DomesticPartner relationship. You are not required to  
> have one, but if you do, you can have only exactly one. And if you  have 
> one, no one else can share it (legally).
> 
>>
>> In any case I usually use a join table for optional 1-to-1  
>> relationships anyway.  It provides better forwards compatibility.   
>> For instance, if you ever have to upgrade that relationship to 1-to- 
>> many, you won't need to migrate anything.
> 
> 
> I agree with you that a join table for optional 1-1 relationships is  
> the better choice. Then the join table columns can be defined as  unique 
> and you simply omit the values that otherwise would have to be  null.
> 
> Craig
> 
>>
>> Daniel
>>
>>
>>
>> -- 
>> Daniel Noll
>>
>> Nuix Pty Ltd
>> Suite 79, 89 Jones St, Ultimo NSW 2007, Australia    Ph: +61 2 9280  0699
>> Web: http://nuix.com/                               Fax: +61 2 9212  6902
>>
>> This message is intended only for the named recipient. If you are not
>> the intended recipient you are notified that disclosing, copying,
>> distributing or taking any action in reliance on the contents of this
>> message or attachment is strictly prohibited.
> 
> 
> Craig Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
> 


Mime
View raw message