How to create unique numbers for a primary index field (Views: 101)


Using D4, Paradox 7 and a peer-to-peer network on Win95/ 98, I am currently thinking about the problems of using AutoInc fields as primary indexes to avoid key violations. On balance, I feel that it is probably best to avoid potential problems by choosing an alternative primary index system. But what are the alternatives? Using a DateTime field as the unique primary index or use a number that is incremented in code?


If you need a unique number for a primary key create a single-field-single-record table to hold the last used value and call the following function when you need a new number.

function dgGetUniqueNumber(LastNumberTbl: TTable): LongInt;
{Gets the next value from a one field one record table which stores the last used
value in its first field. The parameter LastNumberTbl is the table that contains the last used number.}
  ntMaxTries = 100;
  I, WaitCount, Tries: Integer;
  RecordLocked: Boolean;
  ErrorMsg: string;
  Result := 0;
  Tries := 0;
  with LastNumberTbl do
    {Make sure the table contains a record. If not, add one and set the first field to zero.}
    if RecordCount = 0 then
      Fields[0].AsInteger := 0;
    {Try to put the table that holds the last used number into edit mode. If calling Edit
    raises an exception wait a random period and try again.}
    while Tries < ntMaxTries do
      on E: EDBEngineError do
        {The call to Edit failed because the record could not be locked.}
        {See if the lock failed because the record is locked by another user.}
        RecordLocked := False;
        for I := 0 to Pred(E.ErrorCount) do
          if E.Errors[I].ErrorCode = 10241 then
            RecordLocked := True;
        if RecordLocked then
          {Wait for a random period and try again.}
          WaitCount := Random(20);
          for I := 1 to WaitCount do
          {The record lock failed for some reason other than another user has the
          record locked. Display the BDE error stack and exit.}
          ErrorMsg := '';
          for I := 0 to Pred(E.ErrorCount) do
            ErrorMsg := ErrorMsg + E.Errors[I].Message + ' (' + IntToStr(E.Errors[I].ErrorCode) + '). ';
          MessageDlg(ErrorMsg, mtError, [mbOK], 0);
    if State = dsEdit then
      Result := Fields[0].AsInteger + 1;
      Fields[0].AsInteger := Result;
      {If the record could not be locked after the specified number of tries raise an exception.}
      raise Exception.Create('Cannot get next unique number. (dgGetUniqueNumber)');

<< Back to main page