Uhm, not to beat a dead horse... Some feel that Derby is behaving properly, others, like myself and you feel that Derby is not adequately performing.
There is a very simple solution. When you have an insert on a table with an identity column, which has GENERATE BY DEFAULT ..., if the row can be inserted, if the row contains a value in the identity column and its value is greater than the CBV (current base value) in the sequence, then reset the current base value to the inserted value. (This will ensure that the next number from the sequence will be the largest value in the table and should be inserted without generating an exception.) NOTE: When I say generate an exception, I mean specifically that you will not get an exception of the sequence value not being unique to the table. Now if you don't believe me, then we can always bet bottle of scotches. And you can thank IBM's IDS for the solution. Its how their SERIAL/SERIAL8 datatypes work. [A Serial Data type is an integer that has an associated sequence and identity index as part of the data type.] This is the simplest solution, lowest cost solution, and doesn't violate the definition of an SQL sequence. But hey! What do I know? ;-) -G > -----Original Message----- > From: Jimisola Laursen [mailto:[EMAIL PROTECTED] > Sent: Wednesday, June 28, 2006 7:05 PM > To: [email protected] > Subject: Re: "generated by default" question > > > I'll second that as it sure does. We just switched from HSQLDB to Derby > due > to Derby's better handling of isolation levels, but this issue is really > time consuming as we have to rewrite many of our tests. > > Jimisola > -- > View this message in context: http://www.nabble.com/%22generated-by- > default%22-question-tf1701190.html#a5095042 > Sent from the Apache Derby Users forum at Nabble.com.
