Basic sequence question

[u3666...@spawnkill.ip-mobilphone.net]

I'm an Oracle sequence novice. I have several tables with primary
key IDs that need to be auto-incremented, and sequences appear to be
the best way to do that. My question is, do I need to create a separate
sequence object for each table? I would think that I do, so that the
objects can 'remember' their current value. Am I correctly
interpreting their function?

Thanks in advance...

--
Sent by g from yahoo subpart of com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com/cgi/content/new

[Avery]

Yes create one for each.

<u3666...@spawnkill.ip-mobilphone.net> wrote in message
news:l.982311650...@c1026384-a.crvlls1.or.home.com...

[David Fowler]

On Oracle 8i you wouldn't have to, The tables don't need to each have a
sequence. A single sequence for all will work. Each table has insert trigger
assigning key from the sequence. The insert statement uses the RETURNING
clause with the PK key column value to the caller. A surrogate PK is just
key to ensure uniqueness.

If the insert fails for whatever reason or is rolled back. The sequence is
still incremented and doesn't "remember" this case. So you still don't know
where your are with a current value.
The RETURNING clause makes select seq.currval unnecessary. In fact with a
single seq. The triggers should call a function/procedure which references
the seq. Users don't have direct access to the seq at all.

hth
Dave Fowler

"Avery" <av...@aloha.net> wrote in message
news:yijj6.21448$_D.30...@typhoon.we.rr.com...

[Steve Bell]

Hi,
Although it may be common in practice to create a sequence for each
table that requires it,
it is not a requirement...you can use one sequence in many tables...it's
a seperate database object.

Hth,

Steve

[ledz]

The advantage of creating one sequence for all your is you have a then
unique identifier for all the object in you database. It might not be
useful now, but it doesn't cost anything, and could pay off in spades later.

"Steve Bell" <swayn...@sympatico.ca> wrote in message
news:3A911B3...@sympatico.ca...