Re: sequence advances on failed insert

On Thu, Apr 07, 2005 at 11:27:41PM -0400, Sven Willenberger wrote:
>
>
> David Fetter presumably uttered the following on 04/07/05 20:16:
> >On Thu, Apr 07, 2005 at 07:59:52PM -0400, Matthew Terenzio wrote:
> >
> >>I'm noticing that a sequence is advancing even if the insertion
> >>fails.  Is this weird or expected?
> >
> >
> >It's expected.  Sequences are guaranteed to generate unique IDs.
> >These happen to be an increasing sequence of integers, but there is
> >no attempt to make this a gap-free sequence, and your apps should
> >not depend on the actual value of said ID.
>
> I assume by "not depend on the actual value" that one should not
> assume that the next value will be one increment higher than the
> current highest value in the table;

Yes.

> because it is guaranteed to be unique, I would think it to be an
> excellent way to assign a customer id, for example, which can then
> be referenced (foreign key, etc) by other tables after a new record
> is added.

It's designed for just that purpose :)

> Unless there is some other reason one should not use a sequence
> value as any type of identifier?

Relational purists sometimes insist that artificial keys cause more
problems than they solve, but I'm not in that camp most of the time.

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!