25 September, 2002
Vladimir Trukhin
Senior Software Engineer
JSC "Votkinsk Hydroelectric Power Plant"
Fax: +7 (34241) 63297
E-mail: vlt@votges.ru

Primary keys without problems

When somebody got regular “Unique key violation” message, perhaps he would say: “Why doesn’t database do it itself”? He’s right 1000 times. Database must find a proper key value. We should teach it if it doesn’t know how to do such operation.

The “Unique key violation” message means that the attempt of repeated usage of primary key value has occurred whereas such key must be unique for each record in the table. There are many causes of it, form a usage of the APPEND BLANK command up to non-coordination of the program code, because different developers can create their own applications for same database. In last case the calls of the primary key generating functions is hided in many segments of the program code. This circumstance will really lead to mistakes on changing of the key generating rules. It is a “causa mortis” to find all this panes and synchronize them.

       The more reliable way is a usage of the call of the key generating function as default value of the key field. However when several such functions are written and debugged the wish for holding of the one only function appears. Such function could generate the keys for all tables in all projects. Many developers do it creating functions, which take table aliases, field names and other values as parameters.

        This approach works well on a few amount of tables and projects. However when database development is putted on a conveyor then it is necessary to have a function, which can generate primary key for any table without additional directions from something else.

The side what wind blows from. 

The first thing, what is needed to do beginning a research in this way, is to determine the source of the next value of the primary key. It is possible to get key value directly from table taking the incremented key value of the last record of the table when it ordered by key field. I call this method “Source Method”.

        Early I widely used this method. It duly serves me and this article isn’t first my article on this subject. There was else one, “The easy way to form a primary key”. It was published at http://www.oocities.org/vhpcg/primkey.html. I listened a series of censorious remarks at Universal Thread about capacity for work of such technology. In contrast to “Source Method” Craig Bernston points to his technology of the primary key generation that he describes in the “KB006: Primary Keys” article, http://www.craigbernston.com/Articles/kb006.htm. Its main idea is an existence of special table, something like a dictionary, which store last used values of primary keys. I’ve called this approach “Dictionary Method”.

Source or Dictionary?

Every one of these methods has own advantages and disadvantages. It is impossible to unambiguously say what is better.

       “Source Method” provides new key value analyzing the key values really existing in the table. Got value will be correct for any table even somebody replace the table with it backup copy. However this method brings problems, which are linked with updateable views usage. In this case it has to add a record directly into the table and then to requery the view. Otherwise there is a chance of situation when different processes will get the same key value.

        “Dictionary Method” supposes that an additional table is present, that this table stores the key values for all tables of a database. Furthermore it takes some efforts to provide a synchronization of data tables with key table. Anybody cannot guarantee that in the one nice moment there is no yesterday backup table of keys among today tables. Nevertheless this method provides unique key value for all table buffering types and updateable views. 

        When all arguments are weighed it is logically to do a step to the side of “Dictionary Method” because it really gives more freedom on application development. 

Structure of the keys table

As “Dictionary method” is chosen it is a time to create a keys table. The record structure of this one is presented in the next table: 

The structure of the KEYS.DBF table.

        It is enough to find a proper record, increment a key value and return it to the calling procedure to get the primary key value from this table. The call of the key generating function looks like this:

_GetKey(lcKeyName)

        However I should like to write this call somewhat shorter to lighten a database development and exclude unfortunate mistakes coming from incorrect key names. I should like to write it as next call: 

_GetKey()

Last arrangements

It is necessary to determine the field name of the primary key to organize a function call without any parameters. Visual FoxPro can help in this problem itself. It has an outstanding feature. I’ve catch sight of it when I had been debugging the database rules, triggers and functions procuring default values. Table alias becomes current one when the default value function is called.    

        There is another circumstance, which can be used to reach a goal. It is a primary key character. As a rule an expression of the key coincides with the field name of this key.

        Here is a definition of the primary key name getting function:

Function _GetKeyName()
local lcPrimaryKey, lnCount
lcPrimaryKey=''
** scan all tags
for lnCount = 1 TO tagcount()
    if !empty(tag(lnCount))
        ** find primary key tag
        if primary(lnCount)
            ** get field name
            lcPrimaryKey=sys(14,lnCount)
        endif
        exit
    else
        exit
    endif
endfor
return lcPrimaryKey 

It is a time to define the key value getting function when the key value getting way is known.

FUNCTION _GetKey(lcKey)
* a developer can point key name directly
* if key expression doesn’t coincide with field name
IF VARTYPE(lcKey)!='C'
    lcKey=_GetKeyName()
ENDIF
LOCAL lnLength
lnLength=LEN(&lcKey)
IF !USED('GET_KEYS')
    USE KEYS IN 0 ALIAS GET_KEYS;
        ORDER TAG KEY SHARED AGAIN
    IF !USED()
        RETURN ‘’
    ENDIF
ENDIF
lcKey=UPPER(ALLTRIM(lcKey))
IF !SEEK(lcKey,'GET_KEYS','KEY')
  RETURN 0
ENDIF
DO WHILE !RLOCK('GET_KEYS')
ENDDO
LOCAL lnNewKey
lnNewKey=get_keys.Value+1
replace VALUE WITH lnNewKey IN GET_KEYS
UNLOCK IN GET_KEYS
RETURN PADL(ALLTRIM(STR(lnNewKey)),lnLength,'0')

A fly in the ointment

The offered way to form a primary key value has some disadvantages linked with “Dictionary Method” usage:

• There is a chance that the value of the last used key in the keys table doesn’t correspond with keys in the data table.

• Somebody must create records in the keys table and set correct key values when new table is added into database or keys table just created.

Next function can make up these deficiencies. It determines the primary key value using “Source Method” and stores it into keys table for specified key. The function will add such record into table if this key record doesn’t exist. It’s appropriate to use this function on new table creating and to correct values in the keys table.

FUNCTION RepairKey(lcTable, lcKey)
lcTable=ALLTRIM(lcTable)
lcKey=ALLTRIM(UPPER(lcKey))
USE &lcTable IN 0 SHARED ORDER tag &lcKey ALIAS SETKEY AGAIN
USE KEYS IN 0 SHARED ORDER TAG KEY ALIAS REPKEY AGAIN
GO BOTTOM in SETKEY
LOCAL lcValue, lnValue
lcValue='setkey.'+lcKey
lcValue=&lcValue
lnValue=VAL(lcValue)
IF SEEK(lcKey,'REPKEY','KEY')
  IF RLOCK('REPKEY')
       IF DELETED('REPKEY')
              RECALL
       ENDIF
       replace KEY WITH lcKey,;
              VALUE WITH lnValue;
              IN REPKEY
       UNLOCK IN REPKEY
  ENDIF
ELSE
  IF FLOCK()
       INSERT INTO REPKEY (KEY, VALUE);
                     VALUES (lcKey, lnValue)
       UNLOCK IN REPKEY
  ENDIF
ENDIF
USE IN SETKEY
USE IN REPKEY
RETURN

Usage of the technology

It is enough to place the _GetKey() and _GetKeyName() functions into stored procedures of the database to use this technology. Then define a default value of the primary key as call of the _GetKey() function for each table of the database (see Figure 1).

Figure 1. Primary key default value definition in the table designer.

Now  it is possible to add records with “INSERT INTO” and “APPEND BLANK” commands and even with “hot keys” CTRL+Y.

Home Page | Technologies | Specialists | Download | Links | Sign Guestbook | View Guestbook