db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendle...@amberpoint.com>
Subject Re: duplicate rows on unique constraint
Date Thu, 22 Jan 2009 00:08:05 GMT
Glenn Bradford wrote:
> The following DDL allows two rows to be inserted with the same value in 
> a column when a unique constraint on that column should prevent it. 

Hi Glenn, thanks for bringing this up, it seems quite interesting!

Your script produces the same behavior for me in my environment.
This definitely seems like wrong behavior to me; I think it would be
great if you could file this in the JIRA tracking system, together with
your script.

I think that the problem is related to the fairly new feature of Derby
which allows definition of a unique constraint on a null-able column
https://issues.apache.org/jira/browse/DERBY-3330

On versions of Derby prior to 10.4, your script produces:

ERROR 42831: 'RANK' cannot be a column of a primary key or unique key because it can contain
null values.

This suggests that a workaround might be to change the definition
of your table so that the 'RANK' column is required to be non-null.

When I changed your script to read:

     rank int not null,

the script then runs successfully without producing a duplicate rank value
on both 10.2 and 10.4 versions of Derby.

Hope this helps,

bryan

Mime
View raw message