db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bruehlicke <bruehli...@gmail.com>
Subject Re: Version column generated by default ?
Date Fri, 29 Feb 2008 14:15:04 GMT
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 <V.Narayanan@sun.com> 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
>

Mime
View raw message