Re: Adaptation of Auto Increment to a Compound Key

jjo135_at_hotmail.com (John O'Hara) writes:

> I was curious if anyone knows if it is possible to implement the
> following in Oracle:
>
> MySQL has a feature called Auto-Increment, which is similar to Oracle
> sequences, however it was one some additional features that (I don't
> believe) sequences can provide.
>

<snip>  

> we get the resulting table
>
> filename revision
> foo.txt 1
> bar.txt 1
> foo.txt 2
> foo.txt 3
> bar.txt 2
>
> That is, a separate sequence is maintained for each filename. I have
> not found a way to do this in Oracle, short of querying the table each
> time for max(revision) where filename=? and incrementing it, but this
> seems error prone. Is there native support for this, or would I be
> forced to write some sort of trigger?
>

I don't think you can do this very easily in Oracle - probably the only way would be to create some sort of trigger. However, you would need a different sequence for each filename and that could be a problem if you are dealing with lots of different files.

Something else to note is that a sequence in oracle is NOT guaranteed to have no gaps - from vague memories, I think auto increment in MySQL gives you consecutive numbering with no gaps (unless you delete records).

Tim Received on Sun Nov 24 2002 - 16:41:20 CST