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
