[ https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12561977#action_12561977 ]
Anurag Shekhar commented on DERBY-2212: --------------------------------------- I have created a seperate issue DERBY-3330 to handle unique constraint over nullable field and moving my works there. I will not be working on this issue for 10.4 release. > 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 > Assignee: Anurag Shekhar > Attachments: derby-2212preview.diff, derby-2212preview2.diff, > FunctionalSpec.html, FunctionalSpecV3.html, FunctionalSpecV3_comment.html, > FunctionlaSpecv2.html > > > 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 is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.