Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: NextNumbers Tables

Re: NextNumbers Tables

From: Alan Gutierrez <ajglist_at_izzy.net>
Date: Mon, 10 Feb 2003 14:46:21 GMT
Message-ID: <1XO1a.20790$tQ1.1398507@news2.east.cox.net>


stu wrote:

> Ive heard ppl suggest a next numbers table to generate a unique ID for a
> surrogate primary key. Something like:
> CREATE TABLE STUART.NEXTNUMBERS (TABLENAME VARCHAR (20 ) NOT NULL,
> NEXTNUMBER BIGINT NOT NULL)
>
> They then increment the next number when one is taken. My question is:
>
> If the number is just a unique meaningless number why not have:
> CREATE TABLE STUART.NEXTNUMBER (NEXTNUMBER BIGINT NOT NULL) and all tables
> use the same next number. Just though of one reason as im writing this.
> This would mean all inserts (even across different tables) would need t be
> serialised. This is a pretty good reason not to use this!
>
> Any more thoughts on a next numbers table for surrogate keys?

I don't know if you are listening but, don't use a next number. Find a a natural primary key. If you absolutely cannot find one, use the sequence facilities provided by your db. Now you are going to have to find some other way to check for duplicate entries in your database. If there is no sequence facility provided by your db, get a different db. If you cannot get a different db then do this:

INSERT INTO NEXTNUMBERS VALUES(0);
UPDATE NEXTNUMBERS
   SET NEXTNUMBER = (SELECT MAX (NEXTNUMBER) + 1 FROM NEXTNUMBERS); SELECT MAX(NEXTNUMBER) FROM NEXTNUMBERS; But don't do that.

-- 
Alan Gutierrez - ajglist_at_izzy.net
Received on Mon Feb 10 2003 - 08:46:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US