Delphi DB - Autoincrementing Fields

• see also:
  • Delphi Programming Main Index

In Delphi applications, the use of tables containing fields that
autoincrement, or change automatically in some manner unknown to
the application, can be problematic. Paradox, InterBase, Sybase
and Informix tables all provide means of inserting or updating
field values automatically, without intervention from the front-
end application. Not every table operation is affected by this
mechanism, however. So, this document will attempt to provide a
guideline for dealing with issues relating to the use of such
field types in Paradox 5.0, Informix 5.x, MS/Sybase SQL Server
4.x, InterBase 4.0 and Local InterBase tables.

For each table type, a different mechanism provides this
behind-the-scenes behavior. Paradox tables support an
Autoincrement field type. When new records are added to such
tables, the Borland Database Engine determines the highest
current value in that column, adds one, and updates the new row
with the new value.

For Informix tables, this behavior is provided by an
Informix-specific field type called Serial. Serial columns
differ from Paradox Autoincrement fields in that their values may
be changed, while Autoincrement columns are read-only.

InterBase and MS/Sybase SQL Server tables do not support a special
type for this kind of behavior, but may employ triggers to
accomplish the same task. Triggers are specialized procedures
that reside on the database server and automatically execute in
response to events such as table inserts, updates and deletes.
The use of tables with associated triggers can be particularly
problematic, since triggers are capable of doing much more than
just incrementing column values.

The three areas that are affected by these field types are simple
inserts, batchmoves, and table linking.


Handling Update and/or Append BatchMoves
-----------------------------------------------------------------

Paradox Tables

Since the Autoincrement field type is a read-only type,
attempting to perform a batchmove operation with such a column in
the destination table may cause an error. To circumvent this,
the TBatchMove components Mappings property must be set to match
source table fields to the target destination fields excluding
the destination table's Autoincrement field.

Informix Tables

Batch moving rows to Informix tables with Serial columns will not
cause an error in and of itself. However, caution should be used
since Serial columns are updateable and are often used as primary
keys.

InterBase Tables
MS/Sybase SQL Server Tables

Triggers on InterBase and SQL Server tables may catch any
improper changes made to the table, but this depends strictly
upon the checks placed in the trigger. Here again, caution
should be used since trigger-updated columns are often used as
primary keys.


Linking Tables via MasterSource & MasterFields
-----------------------------------------------------------------

Paradox Tables
Informix Tables

If the MasterFields and MasterSource properties are used to
create linked tables in a master-detail relationship and one of
the fields in the detail table is an Autoincrement or Serial
field, then the matching field in the master table must be a Long
Integer field or a Serial field. If the master table is not a
Paradox table then the master table's key field may be any integer
type it supports.


InterBase Tables
MS/Sybase SQL Server Tables

Linking with these tables types presents no particular problems
relating to trigger-modified fields. The only necessity is
matching the appropriate column type between the two tables.


Simple Inserts/Updates
-----------------------------------------------------------------

Paradox Tables

Since Paradox Autoincrement fields are read-only, they are not
typically targeted for update when inserting new records.
Therefore, the Required property for field components based on
Autoincrement fields should always be set to False. This can be
accomplished from within Delphi, using the Fields Editor to
define field components at design time by double clicking on the
TQuery or TTable component or at runtime with a statement similar
to the following.

Table1.Fields[0].Required := False;

or

Table1.FieldByName('Fieldname').Required := False;


Informix Tables

Although Informix Serial fields are updateable, if their
autoincrement feature is to be used, then the Required property
of field components based on them should be set to False. Do
this in the same manner described for Paradox Tables.

InterBase Tables
MS/Sybase SQL Server Tables

Handling inserts on these trigger-modified table types requires a
number of steps for smooth operation. These additional steps are
particularly necessary if inserts are accomplished via standard
data-aware controls, such as DBEdits and DBMemos.

Inserting rows on trigger-modified InterBase and SQL Server
tables may often yield the error message 'Record/Key Deleted'.
This error message appears despite that the table is properly
updated on the server. This will occur if:

1. The trigger updates the primary key. This is not only
likely when a trigger is used, but is probably the most
common reason for using a trigger.

2a. Other columns in the table have bound default values.
This is accomplished with the DEFAULT clause at table
creation in the case of InterBase. or with the
sp_bindefault stored procedure in SQL Server.

or

2b. Blob type fields are updated when a new row is inserted.

or

2b. Calculated fields are defined in an InterBase table.


The fundamental cause for this is that when the record (or
identifying key) is changed at the server, the BDE no longer has
means of specifically identifying the record for re-retrieval.
That is, the record no longer appears as it did when it was
posted, therefore the BDE assumes that the record has been
deleted (or the key changed).

Firstly, the field components of trigger-modified fields must
have their Required property set to False. Do this in the same
manner described for Paradox Tables.

Secondly, to avoid the spurious error, order the table by an
index that does not make use of fields updated by the trigger.
This will also prevent the newly entered record from disappearing
immediately after insertion.

Lastly, if requirement 1 above holds but neither 2a, 2b nor 2c
hold, then code similar to the following should be used for the
table component's AfterPost event handler.

procedure TForm1.Table1AfterPost(DataSet: TDataset);
begin
Table1.Refresh
end;

A Refresh of the table is necessary to re-retrieve the values
changed by the server.

If criteria 2a, 2b or 2c cannot be avoided, then the table should
be updated without using Delphi's data-aware controls. This can
be accomplished using a TQuery component targeted at the same
table. Once the query has posted the update, any table components
using the same table should be Refreshed.