Re: Creating a sequence on a composite key
Thursday, December 27, 2001 11:49 AM
A sequence generator usually is expected to only manage the next value from a stack of numeric values and it has no other mechanical functions to facilitate any logical evaluations of other circumstances, so the direct answer to your question is that you desired functionality is not supported by that object type (sequence generators).
However you could impliment logic in your Java interface by using some SQL similar to the following in a prepared statement where you allow the statement to dynamically calculate the next value based on the input value of col1 :
"insert into mytable ( col1, col2 )
select col1, max(col2) + 1 from mytable
where col1 = ? group by col1"
The issue with the above would of course be concurrent sessions generating the same values by executing the same logic at approximately the same point in time.
The other altenative would be to have multiple sequence generators available for each of the possible distinct values that might be inserted into col1 but this seems like it might be a bit cumbersome if there are a lot of potenial values available for col1 or the possible set of values is unknown.