Re: "generated by default" question

[Craig L Russell]

+1

Craig

On Jun 14, 2006, at 4:07 AM, Bernt M. Johnsen wrote:

Let me clearify some items from the SQL 2003 standard related to the
latest mails regarding this issue from Craig and Michael:

1) In the case of "generated always", it should not be possible to
   insert explicit values in identity columns, nor to alter generated
   values.

2) Internal and external sequence generators are by default not
   cyclical (Ch 9.22), so an exception will occur when they are
   exhausted (Ch 9.21)

3) In the case of a rollback, the sequence generator does not "skip"
   values, altough it may appear so. The standard says that "commits
   and rollbacks of SQL-transactions have no effect on the current
   base value of a sequence generator". (Ch 4.21.1) It is the *use* of
   the generated value that is rolled back.

4) A sequence generator which for some reason skips a value (which is
   allowed), will not issue that value in the current cycle, since the
   current base value will be set to the highest value (or lowest if
   the generator is descending) issued in that cycle (Ch 9.21, general
   rules, part 4)

Then again:

5) The behaviour of sequence generators is described independently of

   other words).

6) I can find no relation, whatsoever, defined in the standard between
   the existing values in a column and how the internal sequence
   generator of an identity column behaves.

And finally:

7) How other databases (or Derby for that matter) implement a feature
   is no proof of standards compliancy.

--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway

the context they are used in (as Craig correctly points out but with

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!

smime.p7s
Description: S/MIME cryptographic signature