Creating a sequence on a composite key
1 posts in topic
Flat View  Flat View

Posted By:   Abhay_Shukla
Posted On:   Thursday, December 27, 2001 12:05 AM

I have a table which has composite key of two columns(say 1 and 2). How do I use a sequence which generates next values based on the checks of the two columns. Here is an example:
col1 col2

1 1

1 2

1 3

1 4

2 1 *

2 2

1 5 *

the value marked * is the problem. How do I make sure that for the col1 value 2 I use value 1 for col2...

Also, the number of col2 values may vary for every col1 value

Re: Creating a sequence on a composite key

Posted By:   Bernie_Acs  
Posted On:   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.
About | Sitemap | Contact