Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <30829542.1193137492037.JavaMail.jira@brutus>
Date: Tue, 23 Oct 2007 04:04:52 -0700 (PDT)
From: =?utf-8?Q?=C3=98ystein_Gr=C3=B8vlen_=28JIRA=29?= <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-2212) Add "Unique where not null" to
 create index
In-Reply-To: <20254731.1167917367874.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


    [ https://issues.apache.org/jira/browse/DERBY-2212?page=3Dcom.atlassian=
.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536960 ]=20

=C3=98ystein Gr=C3=B8vlen commented on DERBY-2212:
----------------------------------------

I agree that we should try to avoid incompatibilities with older releases, =
but I am just as worried that a slightly different semantics for unique con=
straints and unique index will be confusing to users  regardless of whether=
 we add another syntax or not. =20

Also, Derby will today not allow the explicit creation of an index if such =
an index already has been implicitly created through the definition of a co=
nstraint.  How should this behave if we keep the old index behavior?  Shoul=
d we allow two different kinds of unique indexes on the same columns?

With respect to upgrade, I assume that after soft upgrade only the  old typ=
e of unique indexes should be created.
After hard upgrade, you should get the new behavior.  The question is wheth=
er we should replace any of the old unique indexes with the  new type on  h=
ard upgrade.  I am not sure I see any good reasons for doing that.  Can not=
 we leave it to the user to decide whether he/she wants to drop and recreat=
ed them?

> 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
>
>
> Derby prohibits creation of unique constraints on nullable colums (as wel=
l 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 rest=
rictive, does not completely comply with SQL standards (both letter and int=
ent) as well as with business needs and intending meaning of NULL values (2=
 null values are not considered as equal, this comparision shall return NUL=
L, and for selection criteria boolean null is treated as FALSE).
> This behavior, as far as I can see, is modelled after DB2 (and differs fr=
om 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 intro=
duced in Derby.
> Regards,
> Oleksandr Alesinskyy

--=20
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.