db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Narayanan <V.Naraya...@Sun.COM>
Subject Re: Version column generated by default ?
Date Fri, 29 Feb 2008 06:13:33 GMT
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