auto-generating primary keys 
Author Message
 auto-generating primary keys

Quote:

> Help please !

> I have a tables which have a field for a unique id no. (ie primary key).
>  I want to automatically generate this number by incrementing the highest previously used number by one.
> I also want the number to take into consideration id no.s used by both current records and those tagged for deletion
> - otherwise {*filter*} things could happen if someone were to recall a deleted record. My code goes:

>    IF RECCOUNT() > 0
>            CALCULATE MAX(employee.employeeid) TO lLastEmployeeID
>            lnewEmployeeID = lLastEmployeeID + 1
>    ELSE
>            lnewEmployeeID = 1000
>    ENDIF

> where employeeid is the id no field in a table called employee. Unfortunately this routine ignores employeeid filed in any deleted records.
> For the record, I did SET DELETED OFF but it doesn't seem to help.

> Any assistance would be greatly appreciated. Thanx in advance.

> Chris.

I have solved the same problem in an Valid:

        CASE m.knopf = 1                && neuer Satz
                SET ORDER TO            && S?tze in physischer (numerierter)
Reihenfolge
                SET DELETED OFF         && gel?schte Datens?tz werden zur
korrekten Nummerierung
eingeblendet
                GOTO BOTTOM                     && zum letzten Satz
                lnstandnr=standort.nstandnr+1   && neue Satznummer erstellen
                INSERT INTO standort (nstandnr) VALUES (lnstandnr)      &&
Satznummer in neuen
Satz einfgen
                SET DELETED ON          && gel?schte S?tze ausblenden
                SCATTER MEMVAR          && kopiert die Datenfelder in
Speichervariablen
                SHOW GETS



Fri, 19 Jun 1998 03:00:00 GMT  
 auto-generating primary keys
Help please !

I have a tables which have a field for a unique id no. (ie primary key).
 I want to automatically generate this number by incrementing the highest previously used number by one.
I also want the number to take into consideration id no.s used by both current records and those tagged for deletion
- otherwise {*filter*} things could happen if someone were to recall a deleted record. My code goes:

        IF RECCOUNT() > 0
                CALCULATE MAX(employee.employeeid) TO lLastEmployeeID
                lnewEmployeeID = lLastEmployeeID + 1
        ELSE
                lnewEmployeeID = 1000
        ENDIF

where employeeid is the id no field in a table called employee. Unfortunately this routine ignores employeeid filed in any deleted records.
For the record, I did SET DELETED OFF but it doesn't seem to help.

Any assistance would be greatly appreciated. Thanx in advance.

Chris.



Fri, 19 Jun 1998 03:00:00 GMT  
 auto-generating primary keys
|Help please !
|
|I have a tables which have a field for a unique id no. (ie primary key).
| I want to automatically generate this number by incrementing the
highest previously used number by one.
|I also want the number to take into consideration id no.s used by
both current records and those tagged for deletion
|- otherwise {*filter*} things could happen if someone were to recall a
deleted record. My code goes:
|
|       IF RECCOUNT() > 0
|               CALCULATE MAX(employee.employeeid) TO lLastEmployeeID
|               lnewEmployeeID = lLastEmployeeID + 1
|       ELSE
|               lnewEmployeeID = 1000
|       ENDIF
|
|where employeeid is the id no field in a table called employee.
Unfortunately this routine ignores employeeid filed |in any deleted
records.
|For the record, I did SET DELETED OFF but it doesn't seem to help.
|

-Chris

The most common method for incrementing a unique id is to have a
separate table that holds the highest used
value for the id.  This way you don't have to worry about deleted
records, you only have to check the table,
increment the value and use that.

--Chris :-{) [MSFT]



Tue, 23 Jun 1998 03:00:00 GMT  
 auto-generating primary keys
Try:
        lcDel = SET('DELETED')
        SET DELETED OFF

        SELECT MAX(Employeeid)+1 ;
           FROM Employee ;
           INTO ARRAY laNext

        lnNewEmployeeID = laNext[1,1]

        SET DELETED (lcDel)

The only problem is that in a multi user environment you may
have problems with this being generated by more than one station
at the same time.

Note: To really work well you need to have a tag on employeeid



Fri, 26 Jun 1998 03:00:00 GMT  
 auto-generating primary keys

Quote:

>Help please !
>I have a tables which have a field for a unique id no. (ie primary key).
> I want to automatically generate this number by incrementing the highest previously used number by one.
>I also want the number to take into consideration id no.s used by both current records and those tagged for deletion
>- otherwise {*filter*} things could happen if someone were to recall a deleted record. My code goes:
>    IF RECCOUNT() > 0
>            CALCULATE MAX(employee.employeeid) TO lLastEmployeeID
>            lnewEmployeeID = lLastEmployeeID + 1
>    ELSE
>            lnewEmployeeID = 1000
>    ENDIF
>where employeeid is the id no field in a table called employee. Unfortunately this routine ignores employeeid filed in any deleted records.
>For the record, I did SET DELETED OFF but it doesn't seem to help.
>Any assistance would be greatly appreciated. Thanx in advance.
>Chris.

I've always found the best way to do this is to keep another table
with the keys in it. In all my apps, I have a table SYSINFO that has
various things in it. One of these is a field for each table and field
that needs a unique number e.g. an Invoice Number. If you use a
numeric field for this (I do, a lot of people don't ... they use
character fields and do tricks to increment them ... you can do
either) you can use a simple function like so:

FUNCTION next_invoice
PRIVATE savesel, n
savesel=SELECT()
IF LOCK()
        SELECT sysinfo
        n=next_inv
        replace next_inv WITH next_inv+1
        USE sysinfo   && To make sure the buffers get flushed back to disk.
ELSE
        WAIT WINDOW "Hoo boy, are you in trouble! The file is locked"
        * Code here to recover from a lock held too long
ENDIF
UNLOCK
SELECT (savesel)
RETURN n

or something like this.
The locking stuff is needed for multi-user. If REPROCESS is set such
that the system will keep trying for many attempts, you should never
get into a lock held situation.

Larry

Larry Bradley
Head, Networks Group
National Research Council
Ottawa, Canada



Sat, 27 Jun 1998 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Auto-incrementing a primary key field

2. Auto Incrementing Primary Key

3. auto-incrementing a primary key

4. auto-incrementing a primary key

5. retrieve a automatic generated primary key

6. How to generate primary keys

7. Help me find the generated primary key!

8. Generate Primary Key - Help

9. Newbie: How to generate Primary Keys!!!

10. How to generate primary keys when selecting items from available listbox to selected listbox

11. Primary Keys, Candidate Keys And Record Re-Usage

12. change primary key ro regular key

 

 
Powered by phpBB® Forum Software