Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss. print.
PRIMARY KEYS WITHOUT PROBLEMS

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. I listened a series of censorious remarks at Universal Thread about capacity for work of such technology. In contrast to “Source Method” Craig Berntson 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:

Field nameField typeLengthCommentsKEYCharacter10Contains the name of the primary key field. KEYS.DBF table must be indexed on this field.VALUE Integer 4Contains last used value of the primary key.
In my projects I use character fields for primary keys because it does their usage in additional compound indexes more simple.
The key value is stored in field, which has integer data type, because primary key fields can have different length.
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:

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:
1: 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.
2: 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.

ABOUT THE AUTHOR: VLADIMIR TRUKHIN

Vladimir Trukhin Vladimir Trukhin is Visual FoxPro developer and author. He has been developing program systems and applications since 1983. He specializes in software system development, user interface design, object oriented programming, developer support, training and other services. Vladimir has written for FoxTalk magazine. Visual FoxPro is his everyday tool and assistant. You can contact him by e-mail at vlt@votges.ru. You can find additional information about Vladimir on home page at http://www.geocities.com/vhpcg/resume.html.

FEEDBACK


Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: