Generating A Unique ID For A Table Row

Though many DBMS offer so-called "autoincrement" fields, which can be used to generate unique identifiers for rows, given the many reported difficulties with such fields, it is usually better to "roll your own" - which can be done easily.

First, establish a table in your database to be used for this purpose. For this example, we'll call it IDSOURCE. It has two fields: TABLEID and NEXTIDFORTABLE. The first field is some special identifier for the table, which is usually the table name. NEXTIDFORTABLE is a value which can be used for a newly added row.

Assuming that this table has persistent fields with default names, here is the code to manage the process. This can go in the AfterInsert or BeforePost handler for your data set:

int NewID;

if (!IDSource->Locate(IDSourceTABLEID.FieldName, UniqueIDForTable, TLocateOptions())
{
   IDSource->Append();
   IDSourceTABLEID->AsString = UniqueIDForTable;
   IDSourceNEXTIDFORTABLE->AsInteger = 2;
   IDSource->Post();
   NewID = 1;
}
else
{
   IDSource->Edit();
   NewID = IDSourceID->AsInteger;
   IDSourceNEXTIDFORTABLE->AsInteger = IDSourceNEXTIDFORTABLE->AsInteger + 1;
   IDSource->Post();
};


// Assign NewID to your table ID field

This fairly simple code can also be placed in a TTable or TQuery descendant, or any other TDataSet descendant by overriding DoAfterInsert or DoBeforePost (remember to call the inherited methods after doing your work, so that the event handlers will get called.

Note that the "then" clause of the "if" statement is a little problematic. In theory, two instances of the app on separate machines could perform that action at the same time. To ensure that does not happen, make sure to install an IDSOURCE table with all possibly needed entries when the system is set up.

If you are using this within a transaction - don't. Apparently at least Local Interbase thinks it is a nested transaction. To avoid the problem, use cached updates on the dataset which would be under transaction control, and then wrap the ApplyUpdates in the transaction. You may end up throwing away some unique IDs if you have to rollback, but you won't experience the problem.