Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 8 of 8 total
Thread AutoInc, to use or not to use
Wed, Dec 12 2007 7:17 PMPermanent Link

Pat
Hi all,

Using both v3.27 and v4.21 b11 (got elevatedb but not using yet)

In the past, I did not use AutoInc (when I add records I just
incremented a value in a Control table and used that in the new record
as say the RecordCountID).

In my present project I did not use the RecordCountID field at all.
NOW I see I should have spent the extra time adding this RecordCountID
field Frown

My options are either spend time coding this ID from a Control table
or just simply adding an AutoInc field.

Main reason for not using AutoInc was I thought somehow if the table
(using AutoInc) got corrupted and had to rebuild, somehow the system
generated AutoInc would get 'screwed up' and a different AutoInc
number would get assigned to the record;  thereby mixing up the
Master-Detail link (because I use the master RecordCountID in the
detail table).

- Are my concerns unfounded?
- In table corruption/rebuilding, does it matter if you have used
AutoInc OR code generated ID field?

Thanks,
Pat
Wed, Dec 12 2007 7:54 PMPermanent Link

"J. B. Ferguson"
Pat,

Here's an answer from Tim in this newsgroup.

-------------------------------------------------------------------

From: "Tim Young [Elevate Software]" <timyoung@mail.elevatesoft.com>
Subject: Re: Autoinc as primary key
Date: Tue, 8 Feb 2000 13:34:29 -0500

David,

<< As some database formats are known to re-assign autoinc fields upon
packing/restructuring, I wonder if it is safe to use a single autoinc
field
as the primary key in DBISAM (I'm also using it for lookups). Are there
*any* circumstances that could lead to a record's autoincremented value
being changed in DBISAM? >>

No, you will not have any such problems using auto-increment fields as
primary index keys in DBISAM.

Tim Young
Elevate Software
www.elevatesoft.com

-------------------------------------------------------------------

I hope that helps...

--
Regards,
Jan Ferguson


Pat wrote:

<<My options are either spend time coding this ID from a Control table
<<or just simply adding an AutoInc field.
<<
<<Main reason for not using AutoInc was I thought somehow if the table
<<(using AutoInc) got corrupted and had to rebuild, somehow the system
<<generated AutoInc would get 'screwed up' and a different AutoInc
<<number would get assigned to the record;  thereby mixing up the
<<Master-Detail link (because I use the master RecordCountID in the
<<detail table).
<<
<<- Are my concerns unfounded?
<<- In table corruption/rebuilding, does it matter if you have used
<<AutoInc OR code generated ID field?
Wed, Dec 12 2007 11:14 PMPermanent Link

Pat
>> - In table corruption/rebuilding, does it matter if you have used
>> AutoInc OR code generated ID field?

>No, you will not have any such problems using auto-increment fields as
>primary index keys in DBISAM.

Thanks for that Jan.

The reply is dated Feb 2000, I guess it still holds true through the
many versions since then?
Thu, Dec 13 2007 3:56 AMPermanent Link

"J. B. Ferguson"
You're welcome Pat.

It works for me with no issues. Steve Forbes, in one post, also stated
it worked no problem for him as well. I'm sure there might have been
more recent posts but I stopped my search at that one. I never
remembered, either before or after that time period, it ever being an
issue.

--
Regards,
Jan Ferguson


Pat wrote:

<<<<<< - In table corruption/rebuilding, does it matter if you have used
<<<<<< AutoInc OR code generated ID field?
<<
<<<<No, you will not have any such problems using auto-increment fields
<<<<as primary index keys in DBISAM.
<<
<<Thanks for that Jan.
<<
<<The reply is dated Feb 2000, I guess it still holds true through the
<<many versions since then?
>>

Pat
Thu, Dec 13 2007 9:44 AMPermanent Link

adam
Dear Pat,

I always, religiously have an ID field of AutoInc datatype as the first field in all my
tables. This gives a single way of getting to any record in any table with just the
knowledge of tablename + ID ... which is very valuable.

I have never "lost" IDs after a corruption, they are always preserved. I have used DBISAM
all over the place, including settings in Africa where power outages occur daily.

In most cases I initialize my ID from 0, but in a few cases I give IDs a "spread",
initializing 1 table from 0 and another from (say) 1,000,000. In this way it is possible
to identify the originating table & record from the ID alone ... though I realise this
design is not really good practice & is not normalized, it can still be a really useful
simple fix for some problems.

--

I _have_ had one strange problem with autoincs after a table corruption. On one of my
systems a table was periodically corrupting and on repair new records would have autoinc
numbers which were not in sequence with previous autoincs.

i.e. The last record would be ID = 12345, after repair the next record might get an ID of
32543321. After that all subsequent records incremenbed normally from this higher range
(i.e. 32543322 etc.)

The reason for the corruption was a combination of intensive use of non-Client-Server
DBISAM with many concurrent users & power-outages. Switching to DBISAM C/S completely
solved the problem.

This is just something to bear in mind.

Adam



Thu, Dec 13 2007 4:06 PMPermanent Link

Steve Forbes

Team Elevate Team Elevate

Hi Jan,

> Steve Forbes, in one post, also stated
> it worked no problem for him as well.

I sure did Smile .. Never had a single problem with DBISAM (or ElevateDB)
autoinc. They have a bad name courtesy of other DB engines, but Tim does it
right!

--
Best regards

Steve

"J. B. Ferguson" <jbNOSPAMfergusonATgmailDOTcom> wrote in message
news:3D75E274-24FF-43CF-AE8E-5EC6BD955017@news.elevatesoft.com...
> You're welcome Pat.
>
> It works for me with no issues. Steve Forbes, in one post, also stated
> it worked no problem for him as well. I'm sure there might have been
> more recent posts but I stopped my search at that one. I never
> remembered, either before or after that time period, it ever being an
> issue.
>
> --
> Regards,
> Jan Ferguson
>
>
> Pat wrote:
>
> <<<<<< - In table corruption/rebuilding, does it matter if you have used
> <<<<<< AutoInc OR code generated ID field?
> <<
> <<<<No, you will not have any such problems using auto-increment fields
> <<<<as primary index keys in DBISAM.
> <<
> <<Thanks for that Jan.
> <<
> <<The reply is dated Feb 2000, I guess it still holds true through the
> <<many versions since then?
>>>
>
> Pat

Thu, Dec 13 2007 7:33 PMPermanent Link

"J. B. Ferguson"
Steve,

I couldn't agree with you more! Way to go Tim!!

--
Regards,
Jan Ferguson


Steve Forbes wrote:

<<Hi Jan,
<<
<<<<Steve Forbes, in one post, also stated
<<<<it worked no problem for him as well.
<<
<<I sure did Smile .. Never had a single problem with DBISAM (or
<<ElevateDB) autoinc. They have a bad name courtesy of other DB
<<engines, but Tim does it right!
Sun, Dec 23 2007 12:13 PMPermanent Link

"David Farrell-Garcia"
No problems using Auto-incs as primary indexes in Dbisam.  There are
some important considerations, howwever. For example if you have an
application that runs in disconnected mode, using a GUID as your
primary key insures that there are no problems when saving back to the
datsabase, nor do you need to fetch the next autoinc value from the
database for Master-Detail relationships.

And if it is decided to restore a Master table rather then an entire
database, in the event of courruption, you must remember to set the
next auto-inc value high enough so that there is no possiblity of
master records linking to the wrong orphanded child records.  No such
problem with Guids.

Other then those considerations, Dbiam auto-incs rock.


--
David Farrell-Garcia
Whidbey Island Software, LLC
Image