You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Xanana Gusmao <gm...@spamgourmet.com> on 2006/12/12 05:08:39 UTC
unique columns must be nullable ?
On this page http://wiki.apache.org/db-derby/HibernateHelp
it says:
"Hibernate Annotations do not work because Derby does not allow a unique column
to be nullable"
Is this (that Derby's unique column must be non-nullable) still true ?
Re: unique columns must be nullable ?
Posted by Rick Hillegas <Ri...@Sun.COM>.
DERBY-653 may be relevant to this issue.
Regards,
-Rick
Bernt M. Johnsen wrote:
>>>>>>>>>>>>> Xanana Gusmao wrote (2006-12-12 04:08:39):
>>>>>>>>>>>>>
>> On this page http://wiki.apache.org/db-derby/HibernateHelp
>> it says:
>>
>> "Hibernate Annotations do not work because Derby does not allow a unique column
>> to be nullable"
>>
>> Is this (that Derby's unique column must be non-nullable) still true
>> ?
>>
>
> That's still true. UNIQUE constraint on nullable columns is optional
> feature T591 in the SQL standard and not implemented in Derby. See
> http://wiki.apache.org/db-derby/SQLvsDerbyFeatures.
>
> For a longer discussion of the topic, see
> http://mail-archives.apache.org/mod_mbox/db-derby-dev/200511.mbox/%3C20051102114053.GB7614@atum01.norway.sun.com%3E
>
>
Re: unique columns must be nullable ?
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Xanana Gusmao wrote (2006-12-12 04:08:39):
> On this page http://wiki.apache.org/db-derby/HibernateHelp
> it says:
>
> "Hibernate Annotations do not work because Derby does not allow a unique column
> to be nullable"
>
> Is this (that Derby's unique column must be non-nullable) still true
> ?
That's still true. UNIQUE constraint on nullable columns is optional
feature T591 in the SQL standard and not implemented in Derby. See
http://wiki.apache.org/db-derby/SQLvsDerbyFeatures.
For a longer discussion of the topic, see
http://mail-archives.apache.org/mod_mbox/db-derby-dev/200511.mbox/%3C20051102114053.GB7614@atum01.norway.sun.com%3E
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: unique columns must be nullable ?
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
Hi,
>>>>>>>>>>>> Michael Bouschen wrote (2006-12-12 10:36:21):
> Hi,
>
> I ran into the same problem (not in the context Hibernate): I tried to
> define a unique index on a nullable column which does not work with
> derby. Here is what the derby doc says in the CONSTRAINTS section:
> UNIQUE Specifies that values in the column must be unique. NULL values
> are not allowed. (see
> http://db.apache.org/derby/docs/dev/ref/rrefsqlj13590.html.
>
> Is there any specific reason for not supporting unique for a nullable
> column? I find it useful to be able to specify a column may or may not
> have a value, but if the value is set it must be unique.
This is an open source project, so I guess the correct answer is "no
one has done it, yet". Seriously: The current index mechanism is not
able to handle null values, so to implement it is not a trivial
undertaking. Anyway, I first step would be to craete a Jira issue for
this (I believe that has not yet been done). That would at least make
the wish for the feature more visible.
Bernt
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: unique columns must be nullable ?
Posted by Michael Bouschen <mb...@spree.de>.
Hi,
I ran into the same problem (not in the context Hibernate): I tried to
define a unique index on a nullable column which does not work with
derby. Here is what the derby doc says in the CONSTRAINTS section:
UNIQUE Specifies that values in the column must be unique. NULL values
are not allowed. (see
http://db.apache.org/derby/docs/dev/ref/rrefsqlj13590.html.
Is there any specific reason for not supporting unique for a nullable
column? I find it useful to be able to specify a column may or may not
have a value, but if the value is set it must be unique.
Regards Michael
> On this page http://wiki.apache.org/db-derby/HibernateHelp
> it says:
>
> "Hibernate Annotations do not work because Derby does not allow a unique column
> to be nullable"
>
> Is this (that Derby's unique column must be non-nullable) still true ?
>
>
--
Michael Bouschen Tech@Spree Engineering GmbH
mailto:mbo.tech@spree.de http://www.tech.spree.de/
Tel.:++49/30/235 520-33 Buelowstr. 66
Fax.:++49/30/2175 2012 D-10783 Berlin
Re: unique columns must be nullable ?
Posted by Craig L Russell <Cr...@Sun.COM>.
On Dec 12, 2006, at 6:42 PM, Randy Letness wrote:
> Mike Matrigali wrote:
>> Out of curiousity what behavior does Hibernate expect when 2 nulls
>> are inserted into a "unique" nullable column?
>>
>>
>
> When you specify a unique constraint in hibernate, I'm pretty sure
> its only used by the schema export tools to generate a unique
> constraint when generating the DDL statements. That is, it doesn't
> care if you insert 2 nulls, it relies on the database to throw an
> exception.
That's my understanding as well. If it's mapped to a toOne
relationship, Hibernate expects that the database will not offer two
rows to populate one relationship, but aside from that, it won't much
care.
On the outbound side, Hibernate might try to insert the second row
with the same value if that's what the object model calls for, and
expect the database to throw an exception.
> Things break when hibernate tries to generate a unique constraint
> on nullable columns in Derby. I guess its not smart enough to
> realize Derby (or any other db) doesn't support this.
Hibernate does have database personality modules to reflect this kind
of stuff. There might be a bug. It's probably worthwhile to make sure
you have the latest code and if it still fails, a Hibernate forum
question would probably result in at least a semi-official response.
Hibernate is open source, and would probably be open to a patch if
there is an issue.
Craig
> I have run into the same problem as I am working on a project that
> supports both MySQL and Derby and MySQL supports constraints on
> nullable columns, while Derby doesn't.
>
> -Randy
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!
Re: unique columns must be nullable ?
Posted by Randy Letness <ra...@osafoundation.org>.
Mike Matrigali wrote:
> Out of curiousity what behavior does Hibernate expect when 2 nulls are
> inserted into a "unique" nullable column?
>
>
When you specify a unique constraint in hibernate, I'm pretty sure its
only used by the schema export tools to generate a unique constraint
when generating the DDL statements. That is, it doesn't care if you
insert 2 nulls, it relies on the database to throw an exception. Things
break when hibernate tries to generate a unique constraint on nullable
columns in Derby. I guess its not smart enough to realize Derby (or any
other db) doesn't support this. I have run into the same problem as I
am working on a project that supports both MySQL and Derby and MySQL
supports constraints on nullable columns, while Derby doesn't.
-Randy
Re: unique columns must be nullable ?
Posted by Mike Matrigali <mi...@sbcglobal.net>.
>
>
> 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
>
"not equal" would not work for the btree. It needs a reproducible
ordering for all key columns so that it can reliably find a particular
key value - this is a recovery requirement.
If someone is interested in supporting this, I think the easiest
solution which would not affect current index performance would be
to internally create "non-unique" indexes for nullable constraints
and at insert time check if the key exists in the btree if it is
not null and throw the exception from higher up than it is now.
One could somehow special case this logic into the btree, but my
worry is that the code then slows down/complicates all unique indexes
for this one special case of allowing 2 nulls.
Re: unique columns must be nullable ?
Posted by Craig L Russell <Cr...@Sun.COM>.
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!
Re: unique columns must be nullable ?
Posted by Mike Matrigali <mi...@sbcglobal.net>.
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!
>
Re: unique columns must be nullable ?
Posted by Craig L Russell <Cr...@Sun.COM>.
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.
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!
Re: unique columns must be nullable ?
Posted by Daniel Noll <da...@nuix.com>.
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.
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.
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.
Re: unique columns must be nullable ?
Posted by Mike Matrigali <mi...@sbcglobal.net>.
Out of curiousity what behavior does Hibernate expect when 2 nulls are
inserted into a "unique" nullable column?
Xanana Gusmao wrote:
> On this page http://wiki.apache.org/db-derby/HibernateHelp
> it says:
>
> "Hibernate Annotations do not work because Derby does not allow a unique column
> to be nullable"
>
> Is this (that Derby's unique column must be non-nullable) still true ?
>
>
>
>
Re: unique columns must be nullable ?
Posted by Jose de Castro <jo...@voxeo.com>.
Xanana Gusmao wrote:
> On this page http://wiki.apache.org/db-derby/HibernateHelp
> it says:
>
> "Hibernate Annotations do not work because Derby does not allow a unique column
> to be nullable"
>
> Is this (that Derby's unique column must be non-nullable) still true ?
>
>
>
I found a way around this issues while converting my application from
MySQL to Derby. It appears that Derby will allow nullable columns in
unique constraints if defined as in a separate statement as the 'CREATE
TABLE'. I have tested this functionality and believe that is functions
as per the SQL specification.
For example, the following statement fails with a message of 'ERROR
42831: 'B_ID' cannot be a column of a primary key or unique key because
it can contain null values':
CREATE TABLE a(
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
b_id bigint default 0,
PRIMARY KEY (id),
UNIQUE (b_id)
);
However, if you create the index in a separate statement is works just fine:
CREATE TABLE a(
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
b_id bigint default 0,
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX AUniqueBID ON a(b_id);
Hope this helps,
Jose de Castro
Sr. Software Engineer
Voxeo Corporation
jose.decastro@voxeo.com