Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss. print.
THE EASY WAY TO FORM A PRIMARY KEY

The Problem
Probably, each developer of the database application on Visual FoxPro received the message «Unique key violation», which signals about attempt of a reuse of a primary key value of the table. After an examining of origin of this message, we begin to use INSERT INTO instead of APPEND BLANK to set a primary key value. Idea of evident definition of a key in a command is correct, but not so good. In this case function calls of a key forming are hidden in many segments of a code of the application that necessarily will result in mistakes at change of rules of a key forming. More reliable way is a use of a function call of a key forming, as default value for a key field of the table. However, when a few such functions are created, we feel a need to have unique function, which would form primary keys for all our tables. Many developers create functions, which receive as parameters the values of the table aliases, names of fields and other meanings. Such approach well works at small quantity of the tables and small quantity of the projects, but if the development of databases is put on the conveyor, then after short time we shall want to have function, which is independent without any instructions can generate a primary key for any table.


The Solution
Let's try to determine individual problems, which are necessary for solving:
- Getting of a name of the primary key field.
- Getting of a name of the primary key tag.
- Ensuring of a search of the next value of a primary key and a store of the record pointer in edited area.
- Calculation of value for a primary key

Before to begin the decision, we shall determine conditions of work of function of a primary key forming:
- We determine function for a database, but not for the concrete program. Therefore, we cannot know a mode of buffering of the table.
- The appending of records in the table can be made even without the program. Thus, the function should independently open the tables, necessary to it, in appropriate aliases.
- As we do not know, in what program this function will work, it should keep constant environment of any program.

So, we shall begin …

Getting of a field name and a tag name of a primary key
Visual FoxPro has remarkable feature. At the moment of calculation of the default value of the field, alias of this table becomes current. Let's take advantage of it for getting of a field name and a tag of a primary key. As these operations are very similar, we shall create for this purpose one common function.
Function _GetPrimaryKey(lcType)
** lcType – defines the type of the return value
** if lcType=NAME, returns a tag name of a primary key
** if lcType=EXPR, returns an expression of a primary key
** An expression of a primary key coincides with a field name for simple key.
local lcPrimaryKey, lnCount
lcPrimaryKey=''
** To scan all tags
for lnCount = 1 TO tagcount()
    if !empty(tag(lnCount))
        ** To find the tag of a primary key
        if primary(lnCount)
            if lcType='NAME'
                ** To get a tag name
                lcPrimaryKey=TAG(lnCount)
            else
                ** To get a field name
                lcPrimaryKey=sys(14,lnCount)
            endif
            exit
        endif
    else
        exit
    endif
endfor
return lcPrimaryKey


Ensuring of a search of the next value
To make search of the next value of a key it is best in separate alias. It will not change a standing of the record pointer. As though we did not make search of the value, necessary to us, the record pointer remain on the added record. Further, developing the application, we can use this alias for intermediate calculations, such as totals and subtotals results.


Let's create for this purpose separate function. It will check existence of this additional area for our table.
FUNCTION _CheckArea(lcTable, lcAlias, lcTag)
LOCAL llSuccess,lnParNum
lnParNum=parameters()
llSuccess=.F.
if !used(lcAlias)
    if lnParNum=3
        use (lcTable) in 0; 
            alias (lcAlias) ;
            order tag (lcTag) ;
            again shared 
    else
        use (lcTable) in 0; 
            alias (lcAlias) ;
            again shared 
    endif
    llSuccess=used(lcAlias)
else
    llSuccess=.T.
endif
return llSuccess 

Calculation of value for a primary key
All preparatory operations are completed, and we can begin calculation of a primary key value. For this purpose we shall create the following function:
FUNCTION _NewKey()
LOCAL lcKey, lcDeleted, lcAlias, lcPrimaryKey, lcField
** To define a name of additional alias with prefix TMP_
lcAlias='TMP_'+alias()
** To define a field name of the primary key
lcField=lcAlias+'.'+_GetPrimaryKey('EXPR')
** To define a tag name of the primary key
lcPrimaryKey=_GetPrimaryKey('NAME')
** Is additional alias opened? 
if !_CheckArea(dbf(),lcAlias,lcPrimaryKey)
    return ''
endif
lcDeleted=set('DELETED')
set deleted off
** To get a value of the last used key
go bottom in (lcAlias)
if bof(lcAlias) or eof(lcAlias)
    lcKey=padl(allt(str(1)),len(&lcField),'0')
else
    ** To increase value for a new key
    lcKey=allt(str(val(&lcField)+1))
    lcKey=padl(lcKey,len(&lcField),'0')
endif
set deleted &lcDeleted
return lcKey

Conclusion
The code of these three functions is necessary to place in stored procedures of a database. Then for each table to define a default value for a field of a primary key as a call of function _NewKey(). After that we freely can add records by commands INSERT INTO, APPEND BLANK and even by hot keys CTRL+Y.

Download code
You can download this article and the program sample here. The download is a zipfile. Its size is 29.338 bytes.

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: