Re: NextNumbers Tables
Date: Mon, 10 Feb 2003 14:46:21 GMT
> 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);
SET NEXTNUMBER = (SELECT MAX (NEXTNUMBER) + 1 FROM NEXTNUMBERS); SELECT MAX(NEXTNUMBER) FROM NEXTNUMBERS; But don't do that.
-- Alan Gutierrez - ajglist_at_izzy.netReceived on Mon Feb 10 2003 - 15:46:21 CET