6
August, 2001
Vladimir Trukhin
Senior Software Engineer
JSC "Votkinsk Hydroelectric Power Plant"
Fax: +7 (34241) 63297
E-mail: vlt@votges.ru
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 tegs
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.
You can download the
article and program sample from file:
newkey.zip.
Size:
13KB.
Also you can download test application from file:
test_pk.zip. Size:
16KB.
