Well, I'm the one that doesn't accept this as a bug/defect since Derby behaves according to the SQL standard. My stand is based on the chapters 4.14.17 and 9.21 in the SQL 2003 (INCITS/ISO/IEC 9075-2-2003 Information technology - Database languages - SQL - Part 2: Foundation (SQL/Foundation)), see below.
The identity column as specified in the standard does not guarantee a
unique value. It gives you a value according to a sequence
generator. If used properly, then the user may get unique values, but
he may also generate duplicates or violate constraints and get an
exception.
You may even write a cyclical identity column like this
GENERATED BY DEFAULT AS IDENTITY (START WITH 0
INCREMENT BY 1
MAXVALUE 2
CYCLE)
Here the generated values for the identity column will be
0,1,2,0,1,2,0,1,2,0,1,2....... (better not have a UNIQUE constraint
on the identity column :-)
(Note: Derby does not support MAXVALUE and CYCLE. Derby does also
require a comma befor INCREMENT).
Chapter 4.14.17:
The columns of a base table BT can optionally include not more than
one identity column. The declared type of an identity column is either
an exact numeric type with scale 0 (zero), INTEGER for example, or a
distinct type whose source type is an exact numeric type with scale 0
(zero). An identity column has a start value, an increment, a maximum
value, a minimum value, and a cycle option. An identity column is
associated with an internal sequence generator SG. Let IC be the
identity column of BT. When a row R is presented for insertion into
BT, if R does not contain a column corresponding to IC, then the value
V for IC in the row inserted into BT is obtained by applying the
General Rules of Subclause 9.21, "Generation of the next value of a
sequence generator", to SG. The definition of an identity column may
specify GENERATED ALWAYS or GENERATED BY DEFAULT.
SG.
And chapter 9.21:
9.21 Generation of the next value of a sequence generator
Function
Generate and return the next value of a sequence generator.
Syntax Rules
None.
Access Rules
None.
General Rules
1) Let SEQ be the SEQUENCE specified in an application of this
Subclause.
2) Let DT, CBV, INC, SMAX, and SMIN be the data type, current base
value, increment, maximum value and minimum value, respectively, of
SEQ.
3) If there exists a non-negative integer N such that SMIN <= CBV + N
* INC <= SMAX and the value (CBV + N * INC) has not already been
returned in the current cycle, then let V1 be (CBV + N *
INC). Otherwise,
Case:
a) If the cycle option of SEQ is NO CYCLE, then an exception
condition is raised: data exception - sequence generator limit
exceeded.
b) Otherwise, a new cycle is initiated.
Case:
i) If SEQ is an ascending sequence generator, then let V1 be
SMIN.
ii) Otherwise, let V1 be SMAX.
4) Case:
a) If SEQ is an ascending sequence generator, the current base
value of SEQ is set to the value of the lowest non-issued value
in the cycle.
b) Otherwise, the current base value of SEQ is set to the highest
non-issued value in the cycle.
5) V1 is returned as the RESULT.
Conformance Rules
None.
>>>>>>>>>>>> Michael Segel wrote (2006-06-09 10:58:25):
> There is a Jira issue already opened.
> Someone pointed to an earlier thread in which Daniel already recommended a
> solution.
>
> I don't find fault with Craig's comment.
> I was thinking in the debate between myself and Bernt.
>
> The issue is that while you can look towards the standards on sequences,
> when you have an identity column that auto-generates a value, you have to
> account for the fact that someone can insert a row that has an unused
> identity value and that the sequence hasn't generated a value that large
> yet.
>
> It's a bug if you ask for Derby to generate an identity value and then it
> fails because the sequence returns a value that is already in use.
> (I.e. someone inserts a row with the identity value of 10 and the sequence
> is only at 6. So that when the sequence returns a value of 10, the insert
> will fail...)
>
> My point is that there is a recognized problem, and it's a defect. Since
> there are two well established corporations which are profiting from selling
> support for Cloudscape/Derby/JavaDB, then they should step up to the plate
> and fix the problem.
>
> It would be different if we were looking for an enhacement or a feature
> request. Then there would be no obligation to implement the
> enhancement/feature....
>
>
> > -----Original Message-----
> > From: Jean T. Anderson [mailto:[EMAIL PROTECTED]
> > Sent: Friday, June 09, 2006 10:33 AM
> > To: Derby Discussion
> > Subject: Re: "generated by default" question
> >
> > Michael Segel wrote:
> > <snip>
> > > Having said that, Daniel already made a suggestion which is in use today
> > by
> > > other databases....
> > >
> > > The simplest solution would be to find the MAX value, rest the sequence
> > to
> > > MAX() +1 and re-insert the row.
> > >
> > > There are other possible solutions.
> > >
> > > It's disappointing that certain people don't accept this as a bug
> >
> > Craig Russell wrote earlier in this thread [1]:
> >
> > > Do you have a good solution that you can write up in detail and post to
> > JIRA? Then someone with an itch can fix it. Do-ocracy in action.
> >
> > Anyone is welcome to open a Jira issue. Jira assistance is at
> > http://db.apache.org/derby/DerbyBugGuidelines.html .
> >
> > -jean
> >
> > [1]
> > http://mail-archives.apache.org/mod_mbox/db-derby-
> > user/200605.mbox/[EMAIL PROTECTED]
>
>
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
pgp6gscW6B6Y5.pgp
Description: PGP signature
