db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: unique columns must be nullable ?
Date Wed, 13 Dec 2006 02:28:58 GMT

On Dec 12, 2006, at 4:28 PM, Mike Matrigali wrote:

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

What would break if we "simply" change this such that NULL always  
compares not equal with all other values including NULL?

Aside from a number of test cases for this particular case...

Craig

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

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