[
https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mike Matrigali updated DERBY-2212:
----------------------------------
comments on 1st draft of functional spec:
1) In section "Proposed behavior of unique Constraint"
There should be more than a set of examples. There should be some description
of the behavior being implemented. Examples are excellent but are not a
complete description of the behavior. For instance even in the 3 column
key case the following cases are not included:
value, null, null
value, null, value
value, value, null
null, value, null
null, value, value
>From the discussion it looks like a key difference between some Db's is
whether (1, null, null) and another (1, null, null) is allowed. So this
would be a good explicit example to use.
I think what you are proposing is that if any column in a key contains a null
then no duplicate checking is performed on insert.
2) I don't agree with the following:
Unique Constraint is internally backed by Index so for Unique Constraint to
support a feature it is mandatory to have a type of index which supports same
behavior. So to support T591 there is a need of a unique index which doesn't
treats nulls as equals ie allows duplicates as long as at least one part of
the key is null.
This describes how derby currently implements unique constraint on non-nullable
columns, but is an internal implementation detail. There is nothing that
says unique constraint on nullable columns must use the same internal
implementation as the one used for non-nullable columns. All that is necessary
is that once a user
declares a unique constraint on a key with a nullable value that it implements
the SQL standard. The standard does not mandate that a "unique index" be
used. There are a number of ways such a feature could be implemented without
it being "mandatory" to have a unique index that allows duplicates.
3) it would be nice to separate implementation from function. The key
functional information I would like to see are:
a) In case of soft upgrade, what is the behavior of create unique index
on nullable columns.
b) In case of soft upgrade, what is the behavior of inserts into
pre-existing unique indexes on nullable columns.
c) in case of hard upgrade, what is behavior of create unique index
on nullable columns.
d) in case of hard upgrade, what id behavior of inserts into pre-existing
unique indexes on nullable columns.
e) will there be new syntax to create index to allow for the creation
of a unique index on nullable columns with different behavior with
respect to nulls than the existing create index behavior.
f) In case of soft upgrade, what is the behavior of creating constraint
on nullable columns.
g) In case of hard upgrade, what is the behavior of creating constraint
on nullable columns.
Discussion on some of these points is going on, but need to understand
intended function before implementation.
My preference for item 3 would be to keep existing behavior of unique
indexes on nullable columns and only implement the new behavior as part
of implementing SQL feature T591, unique constraints on nullable columns.
This avoids a number of upgrade/backward compatibility problems and even
possible performance regressions for existing indexes depending on
implementation.
So the answers would be:
a) no change to current behavior
b) no change to current behavior
c) no change to current behavior
d) no change to current behavior
e) no new syntax necessary, only enabling existing syntax to work when
requesting constraint on nullable columns.
f) Existing error would be thrown.
g) create constraint on nullable collumn would succeed and would implement
SQL standard behavior. Actual implementation specifics to be determined
later.
> 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
>
>
> 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.
