Auto-incrementing a primary key field
Quote:
> I'm wondering how to auto-increment a primary key field for every record
> I add in a table, and if it is possible to prefix this key with
> something like "tv_" followed by the value.
Rather than autoincrement directly from the table, you should have a table
that contains the last value of each primary key, and a function that will
increment the key related to a table or field.
Example :
Keys table
Description (Table, field...) C(20)
LastKey I
I prefer Integer type primary keys, because generally they provide
sufficient range (> 2.10**9 values), and provide better performance
The function could be something like this :
check if keys table is open, otherwise open keys table
seek record corresponding to table or field
lock record
increment lastkey value
unlock record
return new lastkey value
exemple :
insert into mytable (PKfield, otherfield1, otherfield2,...) ;
Values ;
(newkey("mytable"), value1, value2...)
This method ensures you have no duplicate keys, even in a multiuser
environment
Hoping this helps
Christian Desbourse (Belgium)