Hi,
This can not be achieved by using sequences because a sequence does not guard
uniqueness over many columns in many tables.
Why not? I think a sequence can do all that.
No it can't.
John Hutcheson showed that using triggers and constraints on table can
achieve 2 things:
1) Generate a new unique id when it was not provided
2) Guard the uniqueness of a key across multiple tables
What can not be achieved even with that is that let's say the sequence
has generated id's up to integer 300.
The next id it is about to generate is 301.
Now let's say someone inserts a row in one of the tables giving
explicitly 301 as unique id.
Then someone inserts a row into one of the tables without providing the id.
The sequence generates 301 as a new id.
Oops, the constraint is being violated and bang the user gets an error.
So the only way I can think of implementing a distributed key is to
A) either implement it with triggers
B) or implement it as part of h2 source code (adding constucts like
CREATE DISTRIBUTED KEY(...))
Now I implemented that feature as triggers and created functions that
take care of creation and dropping.
I feel that it is a great thing to implement new stuff first outside of
h2 and then later when the concept and implementation is solid
propose it as part of h2 proper.
The only thing I was missing was to return the generated key to the user
correctly in the jdbc api.
- Rami
I need the ability to set the scope id from trigger so that the generated key
would be returned to in the method Statement.getGeneratedKeys()
Do you need multiple sequence values per statement using
Statement.getGeneratedKeys()? This is not supported yet. Could you
explain your use case, and if possible give a real world example?
I was just pondering on the meaning of Stattement.getGeneratedKeys() method.
I realized that a single statement could generate while executing a
single statement
arbitrary amount of keys on arbitrary amount of tables in a database.
Then I said that returning them all would get complicated and thus said
that maybe that discussion
should be left for the time when someone really would benefit from
getting all the generated keys of a statement.
So currently I am happy with the method returning one single generated
key in the ResultSet.
But I need it to be the one I generate in my Distributed Key trigger.
And that is what my change was all about.
Further I am very confident that this change would not break any kind of
compatibility but would simply add
a few lines of code into the source.
- Rami
Regards,
Thomas
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
