Board index » delphi » Paradox Autoincrement Key Field

David W. Body

Delphi Developer
Tue, 21 Apr 1998 03:00:00 GMT

Paradox Autoincrement Key Field

Has anyone had experience deploying a multi-user application which uses a
Paradox autoincrement field as the key field in a master table which has
one or more detail tables associated with it?  I have heard that
master tables which rely on an autoincrement key field sometimes become
corrupted and it is then impossible to get back into synch with the
detail tables.  Can anyone confirm that this is actually a problem?

The alternative would be to generate the key field from a single-record
table which holds the next key number, but this requires a little more
work.  Is this really necessary?

David Body
Big Creek Software

 
Donna Swanso

Delphi Developer
Wed, 22 Apr 1998 03:00:00 GMT

Re:Paradox Autoincrement Key Field


Quote
chris...@{*word*104}port.net (Richard Christman) wrote:

> Why not include an extra integer field in your master table record and
> store a copy of the autoinc field there--on creation. probably best to
> make that your key field too!

> richard

But couldn't that lead to key violations?

Suppose that the following are the order numbers and autoinc field
of those orders that have not been deleted.

OrderNum-AutoInc
1-1
2-2
3-3
5-5

Then, suppose that we rebuild this table now the situation is this:
OrderNum-AutoInc
1-1
2-2
3-3
5-4

If now we go to add the next record then the next autoinc field
generated is going to be 5. Yet, we already have order number 5.
Thus, a key violation.

Arro

Delphi Developer
Thu, 23 Apr 1998 03:00:00 GMT

Re:Paradox Autoincrement Key Field


In <47gmbt$1...@tigger.cc.uic.edu> Donna Swanson <u35...@uicvm.uic.edu>
writes:
Quote

>chris...@{*word*104}port.net (Richard Christman) wrote:

>> Why not include an extra integer field in your master table record
and
>> store a copy of the autoinc field there--on creation. probably best
to
>> make that your key field too!

>> richard

>But couldn't that lead to key violations?

>Suppose that the following are the order numbers and autoinc field
>of those orders that have not been deleted.

>OrderNum-AutoInc
>1-1
>2-2
>3-3
>5-5

>Then, suppose that we rebuild this table now the situation is this:
>OrderNum-AutoInc
>1-1
>2-2
>3-3
>5-4

>If now we go to add the next record then the next autoinc field
>generated is going to be 5. Yet, we already have order number 5.
>Thus, a key violation.

From what I've read to date, and it also seems very logical, is not to
use the autoincrement option for key fields.  It can cause problems
especially on multi user systems.  The best way is to use another file
that supplies a number and is incremented, posted and closed when you
take a number from there.  It serves as a central station for handingt
out the next available number.  An example of that can be found in the
MastApp sample include3d with Delphi.
Ben
Arrow
Wade Auchterlon

Delphi Developer
Fri, 24 Apr 1998 03:00:00 GMT

Re:Paradox Autoincrement Key Field


"David W. Body" <davidb...@dsmnet.com> wrote:

Quote
>I have heard that
>master tables which rely on an autoincrement key field sometimes become
>corrupted and it is then impossible to get back into synch with the
>detail tables.  Can anyone confirm that this is actually a problem?

This is definitely a problem.  A table rebuild will renumber the Auto-Inc column
and destroy the master-detail integrity.

Has anyone seen any commitment from Borland to solving this problem?

Has anyone seen any commitment from Borland to providing a redistributable table
repair program?

Wade Auchterlonie.

Arro

Delphi Developer
Fri, 24 Apr 1998 03:00:00 GMT

Re:Paradox Autoincrement Key Field


In <47hr60$...@status.gen.nz> w...@iconz.co.nz (Wade Auchterlonie)
writes:
Quote

>"David W. Body" <davidb...@dsmnet.com> wrote:

>>I have heard that
>>master tables which rely on an autoincrement key field sometimes
become
>>corrupted and it is then impossible to get back into synch with the
>>detail tables.  Can anyone confirm that this is actually a problem?

>This is definitely a problem.  A table rebuild will renumber the
Auto-Inc column
>and destroy the master-detail integrity.

>Has anyone seen any commitment from Borland to solving this problem?

>Has anyone seen any commitment from Borland to providing a

redistributable table

Quote
>repair program?

>Wade Auchterlonie.

I've been in the programming field for quite a while - over 15 years.
One principal you  must keep in mind.  If there is a workaround to a
problem it is not considered critical.  Hence the autoincrement
feature.  It is merely a feature.  Use when it creates no problems.
Else think of it as non existent.  That's how I survived.
Lighten up felles and you won't get a heart attack.
Happy motoring
Ben
Arrow Software
Arro

Delphi Developer
Fri, 24 Apr 1998 03:00:00 GMT

Re:Paradox Autoincrement Key Field


In <47eacg$...@dsm6.dsmnet.com> "David W. Body" <davidb...@dsmnet.com>
writes:

Quote

>Has anyone had experience deploying a multi-user application which
uses a
>Paradox autoincrement field as the key field in a master table which
has
>one or more detail tables associated with it?  I have heard that
>master tables which rely on an autoincrement key field sometimes
become
>corrupted and it is then impossible to get back into synch with the
>detail tables.  Can anyone confirm that this is actually a problem?

>The alternative would be to generate the key field from a
single-record
>table which holds the next key number, but this requires a little more
>work.  Is this really necessary?

>David Body
>Big Creek Software

Yes it definitely is necessary.  Furthermore the Mastapp demo app
distributed with Delphi resorts to this method.  Using an autoincrement
field is only asking for trouble.  My understaing is that if you
rebuild the file you may run into all sorts of problems.  Just read the
thread on this in this group.

Ben
Arrow Software

Sundial Servic

Delphi Developer
Fri, 24 Apr 1998 03:00:00 GMT

Re:Paradox Autoincrement Key Field


Quote
In article <47hr60$...@status.gen.nz> w...@iconz.co.nz (Wade Auchterlonie) writes:
>>I have heard that
>>master tables which rely on an autoincrement key field sometimes become
>>corrupted and it is then impossible to get back into synch with the
>>detail tables.  Can anyone confirm that this is actually a problem?
>This is definitely a problem.  A table rebuild will renumber the Auto-Inc column
>and destroy the master-detail integrity.
>Has anyone seen any commitment from Borland to solving this problem?
>Has anyone seen any commitment from Borland to providing a redistributable table
>repair program?

At the San Diego conference, Borland indicated that one would be able to
specify the starting number for an autoincrement field in the next release,
and I'm sure that several other problems will be fixed as well.
Mike Fris

Delphi Developer
Fri, 24 Apr 1998 03:00:00 GMT

Re:Paradox Autoincrement Key Field


Quote
Arrow (arr...@ix.netcom.com) wrote:

: field is only asking for trouble.  My understaing is that if you
: rebuild the file you may run into all sorts of problems.  Just read the
: thread on this in this group.

        After discovering this problem quite recently, we resorted to
managing a separate keys table.  Not only does it overcome this problem,
but it also makes the database that much more portable across different
servers (in the event of scaling to client/server).

Mike.

--
==========================================================================
     Mike Frisch                    Internet: mfri...@saturn.tlug.org
     Northstar Technologies                    Compuserve: 76620,2534
     Newmarket, Ontario, CANADA       WWW: http://www.io.org/~mfrisch

degreeAbsolu

Delphi Developer
Sat, 25 Apr 1998 03:00:00 GMT

Re:Paradox Autoincrement Key Field


Quote
sund...@primenet.com (Sundial Services) wrote:
>At the San Diego conference, Borland indicated that one would be able to
>specify the starting number for an autoincrement field in the next release,
>and I'm sure that several other problems will be fixed as well.

speaking of auto-increment fields, does anybody know of any way to
convert a field from an integer to an auto-inc, or vice-versa?

degreeAbsolute

Other Threads

1. Paradox Autoincrement Fields Cause Key Violation -- Help!

2. Key violation when append with autoincrement key field.

3. Key Violation with Paradox Autoincrement

4. Key violation with Paradox autoincrement

5. Q: Paradox, keys and autoincrement

6. KEY VIOLATION on AUTOINCREMENT field ?!?

7. Is there a problem using Autoincrement fields as a primary key

8. Paradox Key Violation - changing a keyed field

9. Key Violation with autoincrement fields

10. autoincrement key field problem