auto-incrementing a primary key 
Author Message
 auto-incrementing a primary key

Hello all,

Sorry, I'm a newbie to VFP 5.0 ...

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.

Thanks very much in advance for your great help and greetings from
Switzerland

Rgis Piccand



Mon, 28 Aug 2000 03:00:00 GMT  
 auto-incrementing a primary key

Regis

There's a good example of this in the TasTrade demo application. Open the
TasTrade database and look at the Default setting for certain tables where
it's set to NewID('tablename'). NewID is a stored procedure in the database
that you can get to from the Database designer meny "Edit stored
procedures... ". Newid maintains a record of the next primary key integer to
use in the table, returns this new key to the primary key field and adds 1
to it. The record is kept in a table that is part of the database. I forget
the name but you'll find it there are only a few tables in this database.
It will thus be inserted autmatically on APPEND BLANK. When you  INSERT INTO
to add a record, do not supply a value for the primary key: INSERT INTO
mytable (name, age) VALUES ('John Doe', 33). The new primary key for John
Doe will be inserted automatically by default by NewId() because you did not
supply a Primary key yourself.
-Anders

This system works very well.

Quote:

>Hello all,

>Sorry, I'm a newbie to VFP 5.0 ...

>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.

>Thanks very much in advance for your great help and greetings from
>Switzerland

>Rgis Piccand



Mon, 28 Aug 2000 03:00:00 GMT  
 auto-incrementing a primary key

Quote:

>Hello all,

>Sorry, I'm a newbie to VFP 5.0 ...

>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.

>Thanks very much in advance for your great help and greetings from
>Switzerland

Greetings to Switzerland!!

You might consider a stored procedure that will increment a value
either based on the largest unused value in your primary key field (do
a search for the max value) or use another table, INI file, or
registry key to increment the value.  Place the call to the stored
procedure in the Insert trigger for the table.

Hope this helps...

Christopher Reed


"The oxen are slow, but the earth is patient."



Mon, 28 Aug 2000 03:00:00 GMT  
 auto-incrementing a primary key

Hi Regis,

Another way might be to have a table of with this type of structure:

KeyName c(15)
Value i(4)

In the KeyName you hold the name of the key field and in the Value you hold
the last/current value of your key.

When you add a record you...

1. Search for the KeyName if you have more than one field in the above
table.
2. Increment the value of that Key
3. Concatenate the 'tv_' with trim(str(value))
4. Check for that to be a unique value in the table ( if not goto step 2 and
repeat )
5. Write to the table

--
Donald

Quote:

>Hello all,

>Sorry, I'm a newbie to VFP 5.0 ...

>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.

>Thanks very much in advance for your great help and greetings from
>Switzerland

>Rgis Piccand



Mon, 28 Aug 2000 03:00:00 GMT  
 auto-incrementing a primary key

Just a note here.

The approach of searching for the largest number (rather than storing the
next number in a table) can result in using the same number twice. If your
table has, say, a id # 10 which you delete and then pack, and it has no id
# 11 or greatere in it, then 10 would be returned and the next record would
get that id.

If in addition there are orphaned children with the foreign key of 10, they
would now be pointing to the wrong parent.

It is cleaner to track the next id in a seperate table for that reason.



Quote:
> You might consider a stored procedure that will increment a value
> either based on the largest unused value in your primary key field (do
> a search for the max value) or use another table, INI file, or

--
Richard Seymour, Anarchy Software, Inc.



Mon, 28 Aug 2000 03:00:00 GMT  
 auto-incrementing a primary key

Agreed!!

Also in a multi-user app two users may get the same max value.

Quote:

>Just a note here.

>The approach of searching for the largest number (rather than storing the
>next number in a table) can result in using the same number twice. If your
>table has, say, a id # 10 which you delete and then pack, and it has no id
># 11 or greatere in it, then 10 would be returned and the next record would
>get that id.

>If in addition there are orphaned children with the foreign key of 10, they
>would now be pointing to the wrong parent.

>It is cleaner to track the next id in a seperate table for that reason.



>> You might consider a stored procedure that will increment a value
>> either based on the largest unused value in your primary key field (do
>> a search for the max value) or use another table, INI file, or

>--
>Richard Seymour, Anarchy Software, Inc.




Mon, 28 Aug 2000 03:00:00 GMT  
 auto-incrementing a primary key

Quote:

>The approach of searching for the largest number (rather than storing the
>next number in a table) can result in using the same number twice. If your
>table has, say, a id # 10 which you delete and then pack, and it has no id
># 11 or greatere in it, then 10 would be returned and the next record would
>get that id.

>If in addition there are orphaned children with the foreign key of 10, they
>would now be pointing to the wrong parent.

>It is cleaner to track the next id in a seperate table for that reason.

I know what you mean.  I usually use the table/INI/registry option
myself; however, I know that some people like the idea of not using an
external resource like another table to autoincrement.

If you did use autoincrement by the Max method, you would need to
program it accordingly to avoid some of the problems.  You should
always look for orphaned children (though you may not be able to get
rid of them all in each instance).  You should also never pack
immediately following deleting the last record.  This can be workable,
though I do not prefer it.

Another technique that one might consider is to base the autoincrement
value of the record number and never delete and pack.  If you "delete"
a record, you blank out all of the fields.  I, however, still prefer
the table/INI/registry method for the sake of consistancy.

Christopher Reed


"The oxen are slow, but the earth is patient."



Tue, 29 Aug 2000 03:00:00 GMT  
 auto-incrementing a primary key



Quote:
> myself; however, I know that some people like the idea of not using an
> external resource like another table to autoincrement.

Yeah, I know...

Quote:
> If you did use autoincrement by the Max method, you would need to
> program it accordingly to avoid some of the problems.  You should
> always look for orphaned children (though you may not be able to get
> rid of them all in each instance).  You should also never pack
> immediately following deleting the last record.  This can be workable,
> though I do not prefer it.

> Another technique that one might consider is to base the autoincrement
> value of the record number and never delete and pack.  If you "delete"

Neither of these methods solve the problem of two users generating the same
id when the add a record at the same time in a multi-user system. In my
experience this becomes a real problem.

Quote:
> a record, you blank out all of the fields.  I, however, still prefer
> the table/INI/registry method for the sake of consistancy.

Agreed!

--
Richard Seymour, Anarchy Software, Inc.



Tue, 29 Aug 2000 03:00:00 GMT  
 auto-incrementing a primary key

My two cents:

I use the external table method to store the next key. To get around two or
more users grabbing the same number, I use the pseudocode below

APPEND BANK        && prepare a new record for adding
< DO THE FORM TO GET USER DATA >
REPEAT
  LOCK_EXTERNAL_KEY_TABLE
UNTIL LOCK_SUCCESSFUL OR TIMED_OUT

IF LOCK_SUCCESSFUL
  ALLOCATE NEW KEYID
  UPDATE KEY TABLE
  RELEASE LOCK
  UPDATE THE NEW RECORD WITH THE NEW KEYID
  SAVE THE NEW RECORD
ELSE   && LOCK FAILED
   << do some processing >>
ENDIF



Tue, 29 Aug 2000 03:00:00 GMT  
 auto-incrementing a primary key

Quote:

>APPEND BANK        && prepare a new record for adding
>< DO THE FORM TO GET USER DATA >
>REPEAT
>  LOCK_EXTERNAL_KEY_TABLE
>UNTIL LOCK_SUCCESSFUL OR TIMED_OUT

>IF LOCK_SUCCESSFUL
>  ALLOCATE NEW KEYID
>  UPDATE KEY TABLE
>  RELEASE LOCK
>  UPDATE THE NEW RECORD WITH THE NEW KEYID
>  SAVE THE NEW RECORD
>ELSE   && LOCK FAILED
>   << do some processing >>
>ENDIF

Sounds pretty good, but... what if the user ABORTS the addition of the new
record AFTER the number has been assigned in the master external table?
Does that number just get burned (i.e. skipped)?


Tue, 29 Aug 2000 03:00:00 GMT  
 auto-incrementing a primary key

Yeah, the number would get tossed out.

You know, for some reason it seems like eveybody wants there to be no gap
in their unique id numbers. Why is that?

If you assign a new database consecutive numbers, the second that one
record is removed there is a gap in the sequence.

I'm a strong believer in the id numbers have no external value, that is
they are unique ID's nothing more.



Quote:
> Sounds pretty good, but... what if the user ABORTS the addition of the
new
> record AFTER the number has been assigned in the master external table?
> Does that number just get burned (i.e. skipped)?



Tue, 29 Aug 2000 03:00:00 GMT  
 auto-incrementing a primary key

Quote:

>>APPEND BANK        && prepare a new record for adding
>>< DO THE FORM TO GET USER DATA >
>>REPEAT
>>  LOCK_EXTERNAL_KEY_TABLE
>>UNTIL LOCK_SUCCESSFUL OR TIMED_OUT

>>IF LOCK_SUCCESSFUL
>>  ALLOCATE NEW KEYID
>>  UPDATE KEY TABLE
>>  RELEASE LOCK
>>  UPDATE THE NEW RECORD WITH THE NEW KEYID
>>  SAVE THE NEW RECORD
>>ELSE   && LOCK FAILED
>>   << do some processing >>
>>ENDIF

>Sounds pretty good, but... what if the user ABORTS the addition of the new
>record AFTER the number has been assigned in the master external table?
>Does that number just get burned (i.e. skipped)?

     The approach that I use is to check succeeding numbers against
the table in question and I start with the LAST number assigned.  If
the record was aborted after key number assignment, then it will be
picked up again.

     Christopher A. Reed commented that referring to the table could
result in orphans being picked up.  This is certainly possible
although you could check against any child tables to avoid this.

Sincerely,

Gene Wirchenko

C Pronunciation Guide:
     y=x++;     "wye equals ex plus plus semicolon"
     x=x++;     "ex equals ex doublecross semicolon"



Wed, 30 Aug 2000 03:00:00 GMT  
 auto-incrementing a primary key

We employ a concept of Process is complete or incomplete.  Therefore there
is no question of Orphaned records in Child tables.  If the previous
operation is incompleted due to any reason ( say due to power failure )
then when the s/w starts again,  the process is completed.

We do this only for single user applications.

I hope I'am not missing something.

Rakesh Kothari
India



Wed, 30 Aug 2000 03:00:00 GMT  
 auto-incrementing a primary key

Matt,

With 2gig (or 4gig if you allow negative values) possible keys in an integer field the
probability that the system you are designing will still be in production is pretty slim.
1000 keys/hr, 24 hrs a day will last 245 years, and you can always include test code that
if you get near the limit to go back and rebuild the keys recovering all the wasted and
deleted ones.

df    - (Microsoft FoxPro MVP) http://www.geocities.com/ResearchTriangle/9834/

Quote:

>Sounds pretty good, but... what if the user ABORTS the addition of the new
>record AFTER the number has been assigned in the master external table?
>Does that number just get burned (i.e. skipped)?



Wed, 30 Aug 2000 03:00:00 GMT  
 auto-incrementing a primary key

Quote:

>Sounds pretty good, but... what if the user ABORTS the addition of the new
>record AFTER the number has been assigned in the master external table?
>Does that number just get burned (i.e. skipped)?

I typically will grab the number at the time of the save so that I do
not waste a value.

Also, a note about more than one user getting the same value.  With
record locking on an external table, this should not be a problem.

Christopher Reed


"The oxen are slow, but the earth is patient."



Thu, 31 Aug 2000 03:00:00 GMT  
 
 [ 15 post ] 

 Relevant Pages 

1. Auto-incrementing a primary key field

2. auto-incrementing a primary key

3. Auto Incrementing Primary Key

4. auto-generating primary keys

5. Auto Increment Field

6. Auto Increment

7. Auto Increment Field

8. Auto-Incrementing

9. Auto Increment Field

10. Auto-Incremented field

11. Auto-increment Field in Foxpro

12. How to make an auto-incrementing index

 

 
Powered by phpBB® Forum Software