Re: Sequence Numbers

Mike,

I tried to reply to you via mail, but your address bounced. So if I may be excused for a breach of netiquette, here is my reply to your post:

There have been several useful posts to this thread; unfortunately, yours isn't one of them. Please don't waste bandwidth with trite comments.

The point is that you should be able to link a sequence with a primary key constraint so you always get a unique number. This would be better than inserting, failing, getting another sequence number, failing, and so on. Since the DB must test the primary key constraint on insert, why not do it on the sequence nextval?

There are cases where you may not be able to use a 20 digit key. One example is selecting into a Pro*C long type (32 bits means only 9 digits). While this is a poor example, since you could just as easily select the key into a character string, there are other valid cases where you can't depend on never reaching "infinity".

I believe that the net is most useful when it generates thoughtful discussion. Next time you get the urge to post a one-liner, just send it to me personally, and I assure you I will give it the attention it deserves.

Thank you for your consideration.

Stan

Your article follows:

In article <3m3thf$e4h_at_dcsun4.us.oracle.com>, mfriedma_at_oracle.uucp (Mike Friedman) writes:
|>Path: watnews1.watson.ibm.com!watnews.watson.ibm.com!locutus.rchland.ibm.com!ibmboulder!juniper.almaden.ibm.com!uunet!gatekeeper.us.oracle.com!dcsun4.us.oracle.com!oracle!mfriedma
|>From: mfriedma_at_oracle.uucp (Mike Friedman)
|>Newsgroups: comp.databases.oracle
|>Subject: Re: Sequence Numbers
|>Date: 7 Apr 1995 17:42:07 GMT
|>Organization: Oracle Corporation, Redwood Shores CA
|>Lines: 18
|>Distribution: world
|>Message-ID: <3m3thf$e4h_at_dcsun4.us.oracle.com>
|>References: <3lpa1l$n17_at_watnews1.watson.ibm.com>
|>NNTP-Posting-Host: mailseq.us.oracle.com
|>
|>In article <3lpa1l$n17_at_watnews1.watson.ibm.com> stan_at_lfs.loral.com (Stan Driggs) writes:
|>
|>>As long as we are discussing sequence numbers, does anyone have an answer to
|>>this question:
 

|>>How can you get a sequence that never issues a number that is already in
|>>use in a table?
 

|>>Sequence numbers are obviously most useful for creating unique numbers that
|>>can be used as keys. The problem is, you run out of numbers at some point
|>>(before infinity), so the sequence must cycle.
|>
|>I would be very impressed to see an application that was able to cycle
|>through a 20 digit sequence number.
|>
|>
|>
|>
|>
Received on Thu Apr 13 1995 - 00:00:00 CEST