Thanx for your feedback.
Unfortunately when I try to create 2 columns with generated int values like
ij> create table test(i int generated by default as identity, name
varchar(40), type varchar(40), version int generated by default as
identity);
ERROR 428C1: Only one identity column is allowed in a table.
which is fair enough - would have been cool if I could create a column
without having to specify the "as identity) - ... well - I will try to see
if I can write a trigger which takes care about this version column - else I
will fall back and have my Java layer controlling this - even though I
really would like to have the Database being responsible for such a version
column.
B-)
On Fri, Feb 29, 2008 at 12:13 AM, Narayanan <[EMAIL PROTECTED]> wrote:
> bruehlicke wrote:
> > Hi,
> >
> > I would like a table something like
> >
> > ID BIGINT (standart generated Identity generated always)
> > NAME VARCHAR(40)
> > VERSION INT
> >
> > Now, I would like to be able to have the system automatically increase
> > the version number, but also allow to use my own and of course if
> > version 1, 2 and 3 are used and the user selects to delete version 3
> > and re-create he will not get verision 4 bu a new version 3
> >
> > QUESTION: Is there a standard "pattern" we use for this kind of
> > columns representing "versions" of a given thing ? Or is is good old
> > "my own algorithm" to maintain this baby ?
> >
> > B-)
> >
> Sorry about the garbled select outputs in the previous reply :-(
>
> I have tried to improve the pasted select outputs in this email
>
> I tried the following experiment with the jars of the latest trunk. If
> you notice after I insert a column
> with the identity column value as 5 the generated column value still
> continues from where it left the increment
> previously.
>
> ij> create table greetings(i int generated by default as identity, ch
> char(50));
> 0 rows inserted/updated/deleted
> ij> insert into greetings values (DEFAULT, 'salut');
> 1 row inserted/updated/deleted
> ij> insert into greetings(ch) values ('bonjour');
> 1 row inserted/updated/deleted
> ij> insert into greetings values (5, 'me');
> 1 row inserted/updated/deleted
> ij> insert into greetings values (DEFAULT,'you');
> 1 row inserted/updated/deleted
> ij> select * from greetings;
>
> I |CH
> --------------------------------------------------------------
> 1 |salut
> 2 |bonjour
> 5 |me
> 3 |you
>
> ij> insert into greetings values (DEFAULT, 'all');
> 1 row inserted/updated/deleted
> ij> insert into greetings values (DEFAULT, 'all');
> 1 row inserted/updated/deleted
> ij> insert into greetings values (DEFAULT, 'all');
> 1 row inserted/updated/deleted
> ij> select * from greetings;
>
> I |CH
> --------------------------------------------------------------
> 1 |salut
> 2 |bonjour
> 5 |me
> 3 |you
> 4 |all
> 5 |all
> 6 |all
>
> 7 rows selected
>
> Also I experimented with deleting a row
>
> ij> delete from greetings where i=6;
> 1 row inserted/updated/deleted
> ij> insert into greetings values (DEFAULT, 'all');
> 1 row inserted/updated/deleted
> ij> select * from greetings;
>
> I |CH
> --------------------------------------------------------------
> 1 |salut
> 2 |bonjour
> 5 |me
> 3 |you
> 4 |all
> 5 |all
> 7 |all
>
> 7 rows selected
>
> So the value continues from 7 and not from 6 as you expect.
>
> The document here
> http://db.apache.org/derby/docs/10.2/ref/rrefsqlj37836.html that
> generated by
> always does not guarantee uniqueness.
>
> You could use a primary key or unique constraint on a column to
> guarantee uniqueness.
>
> I found http://db.apache.org/derby/docs/10.2/ref/rrefsqlj37836.html very
> informative. I am not
> sure if a newer version of the document exists. I guess you could refer
> that for more information.
>
> Narayananan
>
