[
https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16235856#comment-16235856
]
Buğra Gedik edited comment on DERBY-2212 at 11/2/17 2:57 PM:
-------------------------------------------------------------
Thanks for the info [~rhlrx]. Here is the problem I am having with Apache Derby
- 10.11.1.2:
{code}
connect 'jdbc:derby:memory:db;create=true';
ij> create table t(a int, b int);
0 rows inserted/updated/deleted
ij> create unique index ui on t(b);
0 rows inserted/updated/deleted
ij> insert into t(a) values (1);
1 row inserted/updated/deleted
ij> insert into t(a) values (2);
ERROR 23505: The statement was aborted because it would have caused a duplicate
key value in a unique or primary key constraint or unique index identified by
'UI' defined on 'T'.
{code}
Any ideas? Btw, if I create the table as {{create table t(a int, b int,
unique(b))}}, then it works.
was (Author: bgedik):
Thanks for the info [~rhlrx]. Here is the problem I am having with Apache Derby
- 10.11.1.2:
{code}
connect 'jdbc:derby:memory:db;create=true';
ij> create table t(a int, b int);
0 rows inserted/updated/deleted
ij> create unique index ui on t(b);
0 rows inserted/updated/deleted
ij> insert into t(a) values (1);
1 row inserted/updated/deleted
ij> insert into t(a) values (2);
ERROR 23505: The statement was aborted because it would have caused a duplicate
key value in a unique or primary key constraint or unique index identified by
'UI' defined on 'T'.
{code}
Any ideas?
> Add "Unique where not null" to create index
> -------------------------------------------
>
> Key: DERBY-2212
> URL: https://issues.apache.org/jira/browse/DERBY-2212
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.2.1.6
> Reporter: Oleksandr Alesinskyy
> Priority: Major
> Labels: derby_triage10_10
> Attachments: FunctionalSpec.html, FunctionalSpecV3.html,
> FunctionalSpecV3_comment.html, FunctionlaSpecv2.html, derby-2212preview.diff,
> derby-2212preview2.diff
>
>
> Derby prohibits creation of unique constraints on nullable colums (as well if
> only some columns in the constraint list are nullable) and treat nulls in
> unique indexes as normal values (i.e. only one row with null values in
> indexed columns may be inserted into the table). This bahavior is very
> restrictive, does not completely comply with SQL standards (both letter and
> intent) as well as with business needs and intending meaning of NULL values
> (2 null values are not considered as equal, this comparision shall return
> NULL, and for selection criteria boolean null is treated as FALSE).
> This behavior, as far as I can see, is modelled after DB2 (and differs from
> behavior of most other major databases, like SyBase, Oracle, etc.).
> But even DB2 provide some means to alleviate these restrictions, namely
> "UNIQUE WHERE NOT NULL" clause for CREATE INDEX statement.
> It will be very good if such "UNIQUE WHERE NOT NULL" clause will be
> introduced in Derby.
> Regards,
> Oleksandr Alesinskyy
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
