db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-2212) Add "Unique where not null" to create index
Date Thu, 12 Jul 2007 18:15:04 GMT

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

View raw message