db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: "generated by default" question
Date Wed, 14 Jun 2006 11:07:09 GMT
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

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
   the context they are used in (as Craig correctly points out but with
   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

View raw message