[Reply]
Your requirement needs to have a process that is not available in most databases. You
need some logic to not only give you a
sequential number, but to also remember "roll backed" ID's and issue those before
generating new ID's.
This usually requires the creation of a pool of available ID's, and a process to issue
them to calling processes, and to have the issue
confirmed (after a commit), or returned to the pool (after a roll back).
You can do this primarily in the database (stored proc), as part of a middle tier, or
in your client as appropriate.
A stored procedure returning an ID value sounds the best option. It could contain the
code to housekeep the overall process.
Good Luck.
BTW, I am curious as to why the ID's have to be sequential. As a general rule, primary
keys in databases (sometimes called surrogate
keys) should have no meaningful value, only be unique. If you wish to have some
meaning associated with data, then it should be a
separate field, eg invoice number in a financial application, where it is obvious that
the number must be unique, and generally sequential
- although in some cases not essential.
If you are using your unique ID for some process (such as replication or briefcasing),
then would it not be better to have the relevant
process deal with gaps in the sequential number rather than insist upon it in the
first place?
BTW, rules are meant to be broken, and if there is a good reason for the uniqueness,
and sequentiality, then certainly go down the
track you are.
Kind Regards
Gary
At 15:29 on 11/01/2001 Stephen wrote
>To :
>CC :
>From: Stephen Bertram, [EMAIL PROTECTED]
>I need a bulletproof routine to create unique integer IDs for inserting rows
>into multiple tables in a MSSQL 7 database.
>
>The catches are that I want the ID's to be unique in the database, not just
>each table, and the generator must be available to stored procedures.
>
>Also the generator needs to unaffected by transactions - rollbacks should
>not reset the sequence of values - and the generator must not be locked by a
>slow transaction.
>
>Any bright ideas?
>
>Stephen
>
>
>---------------------------------------------------------------------------
> New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
>To UnSub, send email to: [EMAIL PROTECTED]
>with body of "unsubscribe delphi"
========================================================
Gary Benner - Software Developer [EMAIL PROTECTED]
Corporate Software New Zealand Limited Auckland - New Zealand
tel: +64-9 846-6067 (24hr) fax: +64-9 846-6152 mob: (021)-966-992
Software System Design - Consulting - Mentoring - Data Modelling
Client Server - Delphi - Interbase - Oracle - Web-based Technologies
Electronic Automation and Systems - Microcontroller Design & Software
http://www.corporate.co.nz
Ref#: 41006
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
