Jeff Levitt wrote:
Hi all,
There are several JIRA issues relating to docs where I
have created patches, but we don't know if anyone has
looked at them to approve the changes.
[ snip ]
The list below includes those issues with patches I
created awaiting approval.
[ snip ]
http://issues.apache.org/jira/browse/DERBY-275 - doc
support for BY DEFAULT option
I'm not the reporter on this issue, but in looking at this patch and the
surrounding documentation, my comments are as follow:
1) There's a sub-section called "Identity column attributes" under the "CREATE
TABLE" section that talks about identity columns and the generation of automatic
values. It seems to me that either a) the doc for "BY DEFAULT" should go in
that sub-section instead of after the "generated-column-spec" syntax, or b) the
sub-section called "Identity column attributes" should be moved so that it comes
immediately after the "generated-column-spec" syntax. I don't know which is
preferable overall, but my personal feeling is that option b would be most
helpful since that would directly pair the syntax with its description.
2) I think the description of BY DEFAULT columns needs more detail. The
comments you have added say when BY DEFAULT columns are most useful, but they
don't explain _why_ that's the case or even what a BY DEFAULT column does that
makes it different from an ALWAYS column. Examples would be nice, too.
Don't take this as "official" or anything, but here's how I would rewrite the
"Identity column attributes" subsection to account for the "BY DEFAULT" option.
I just marked up the text with tags like "<begin_delete>", "<end_delete>",
"<begin_add>", etc.
----
<begin army_suggestion>
Identity column attributes
For SMALLINT, INT, and BIGINT columns with identity attributes, Derby
automatically assigns increasing integer values to the column. Identity column
attributes behave like other defaults in that when an insert statement does not
specify a value for the column, Derby automatically provides the value. However,
the value is not a constant; Derby automatically increments the default value
<begin_delete> every time a row is inserted. Also, unlike other defaults, you
are not allowed to insert a value directly into or update an identity column.
<end_delete> <begin_add> at insertion time. <end_add>
<begin_add>
There are two kinds of identity columns in Derby: those which are GENERATED
ALWAYS and those which are GENERATED BY DEFAULT.
GENERATED ALWAYS:
An identity column that is GENERATED ALWAYS will increment the default value on
_every_ insertion and will store the incremented value into the column. Unlike
other defaults, you are not allowed to insert a value directly into or update an
identity column that is GENERATED ALWAYS. Instead, you must either specify the
DEFAULT keyword when inserting into the identity column, or else leave the
identity column out of the insertion column list altogether.
For example:
create table greetings (i int generated always as identity, ch char(50));
insert into greetings values (DEFAULT, 'hello');
insert into greetings(ch) values ('bonjour');
Automatically generated values in a GENERATED ALWAYS identity column are unique.
Creating an identity column does not create an index on the column.
GENERATED BY DEFAULT:
An identity column that is GENERATED BY DEFAULT will only increment and use the
default value on insertions where no explicit value is given. This means that,
unlike GENERATED ALWAYS columns, you can specify a particular value in an
insertion statement and that value will be used instead of a generated default.
To use the generated default, either specify the DEFAULT keyword when inserting
into the identity column, or else leave the identity column out of the insertion
column list altogether. To use a specific value, specify the desired value as
part of the insertion statement.
For example:
create table greetings (i int generated by default as identity, ch char(50));
insert into greetings values (1, 'hi'); -- specify value "1"
insert into greetings values (DEFAULT, 'salut'); -- use generated default
insert into greetings(ch) values ('bonjour'); -- use generated default
Note that, unlike a GENERATED ALWAYS column, a GENERATED BY DEFAULT column does
not guarantee uniqueness. Thus, in the above example, the row for 'hi' and the
row for 'salut' will both have an identity value of "1", because the generated
column starts at "1" and the user-specified value was also "1". To prevent
this, you can use the "STARTS WITH" keyword described below. To check for this
condition and disallow it, you can use a primary key or unique constraint on the
GENERATED BY DEFAULT identity column.
<end_add>
By default, the initial value of an identity column is 1, and the amount of the
increment is 1. You can specify non-default values ... [ and so on ]
<end army_suggestion>
----
Of course, you should feel free to reword/rewrite any/all of that--all I'm
really saying is that, to me at least, the documentation of BY DEFAULT needs
more detail, and that the whole section on "Identity column attributes" might be
more helpful if it was moved to the place where the actual syntax is described...
*shrug*
Army
