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
