Re: Generate Random Primary Key

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: R. Paardekam (robin.paardekam_at_tutch.nl)
Date: 06/04/04 

Date: Fri, 04 Jun 2004 15:06:09 +0200

Thanks for your reply. Dropping the table first is out of the question.
This database contains content of several sites with a total amount of
hits around 40 per minute, all over the globe... That would take to long
i'm afraid.

I now made an extra query to fill the gaps of unused ID's. This works
great, but requires more effort from the database:

SELECT TOP 1 t1.File_Id FROM Files t1 LEFT OUTER JOIN Files t2 ON
(t1.File_Id + 1) = t2.File_Id WHERE (t2.File_Id IS NULL)

I added 1 to the result of this query and I got an unused ID!

Works great!

Thanks anyway!

Robin Paardekam
Tutch Mobile Media
The Netherlands

David Portas wrote:

> If the values exceed the maximum for the column then you can widen the
> column by making it a BIGINT or NUMERIC. If the column has a Primary Key
> constraint then you'll need to drop it first and redeclare it afterwards.
> That should be easier than trying to generate random keys.
>
>

Relevant Pages

  • Re: Generate Random Primary Key
    ... This database contains content of several sites with a total amount of ... hits around 40 per minute, ... I now made an extra query to fill the gaps of unused ID's. ... but requires more effort from the database: ...
    (microsoft.public.sqlserver.programming)
  • Re: problem using identity column as primary key
    ... >> I am thinking of creating an identity column to use it as primary key ... More and more programmers who have absolutely no database training are ... the gap in the sequence is not filled in and the sequence ... vin CHARNOT NULL REFERENCES Motorpool); ...
    (microsoft.public.sqlserver.programming)
  • Re: Updating the SQL key value
    ... before creating the database, by the time I've come to pick a primary key, ... articles, each of which must be issued under a particular licence (e.g. ... GPL GNU General Public Licence http://www.gnu ... ...
    (comp.lang.php)
  • Re: Concatenate and Null Values -- Features
    ... Tony Toews dislikes cascade deletes as well as cascade updates, ... fence regarding the use of natural versus surrogate (autonumber) keys. ... Database Normalization Tips ... For optimal database design and performance, the primary key of a table ...
    (microsoft.public.access.reports)
  • Re: Option groups, radio buttons and reports...oh my!!
    ... designing a 'questionnaire' database of using separate columns for the ... referencing the primary key of the Questions and Answers table. ... composite primary key of QuestionAnswers. ... a composite one of RespondentID and QuestionID; ...
    (microsoft.public.access.gettingstarted)