[jira] Updated: (DERBY-2212) Add "Unique where not null" to create index

["Mike Matrigali (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mike Matrigali updated DERBY-2212:
----------------------------------


>From your last comment I can't tell what you plan on changing to implement this.  Are you
planning on changing the btree index implementation, which is a high risk change.  Or 
are you planning on implementing something that uses the exising btree index support
to get what you want.  

At one point I looked at implementing this and started along the path of changing the 
existing btree index implementation but saw that it was going to add a lot of overhead
to the existing paths through the code.  The main issue is that the way "unique" violations are currently coded is that language does an insert and then catches the error from store when it recognizes a problem.  This just happens to be the way derby currently enforces
constraint, one could also enforce constraints by doing a probe using proper locking to 
transactionaly check for existence of duplicate or not and then do subsequent insert 
if no problem.

Off the top of my head it might work to just associate an existing btree index that allowed
duplicates with this new constraint.  And then change the insert code do extra processing
for this special constraint.  It may be easiest to just first do the insert, and then after the
insert do a probe of the index to see if there are more than one non-null value and if
so do a statement backout and return constraint violation.  I think the locking might be
easier than enforcing separate serializable read on probing before the insert.  The 
plus here is no chance to break/slow down exising index paths for all the indexes that
don't care about allowing duplicate null's and non-duplicate other keys.



> 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
>
> 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.