Wiki Home

Using SYS (2015) As APrimary Key


Namespace: SoftwareEng
Does anyone see any problems or advantages of using SYS(2015) as a the value in the primary key field of a table?

I am developing a multi-user application and each table has a primary key field auto populated with sys(2015) as well as foreign keys pointing to the tables that it relates to. The primary keys in those other tables also have sys(2015). This system will have about (max/no more than) 20 users on at any one time. The application will be on a main PC and the users will be remote desktopping in to that computer. (That's what the client wants.)

I decided to use 2015 because I didn't want to create a WHOLE SUBSYSTEM to keep track of what is the current id for this table and that table and incrementing those values and making sure something bad didnt happen thus making the id table get out of sync with the actual primary keys in the tables - I could continue....

What do you think?

Thanks.
Thanks to all who replied. I really appreciate your help!

Not a good idea unless you combine it with other features like SYS(3) or datetime. You'll get repeated values. Why not use AutoInc fields instead? Or else you could use a GUID? -- Alex Feldstein

Alex, thanks for the reply. I wanted to stay away from autoinc because that too can get messed up by someone messing in the table structures.

I am using VFP 9 to develop this system. I went to the GUID link you supplied above and I saw your post:

oGUID = Create Object("scriptlet.typelib") and
cGUID = Strextract(oGUID.GUID, "{", "}" )

Just for the heck of it I pasted the first one into the command window and it worked w/o error. I went to debug and there was oGuid in the Locals window. I tried the second one and it also worked! It made cGuid = B785A13C-9318-47CA-AB78-C332DC86BA20.

This is excellent!

Quick follow up question:
I am using a program to delete the entire new system, the tables and databases and then SQL queries in the program take over and re-create the new system by chopping up their existing data and distributing it all to the new system. Some tables that have no data are created programmatically and the dbc's are also re-created programmatically.

I am selecting appropriate data from their old system and also including as the first field, my field:

SELECT
___SYS(2015) AS PRIM_KEY,;
___...

Would it work if I put:

SELECT
___STREXTRACT(oGUID.GUID, "{", "}" ) AS PRIM_KEY,;
___...

such that each of their records I select from their old system will have a brand spanking new primary key from this guid function?

I am giving them new primary keys because their old "primary key" functionality is messed up and it's complicated and I need something simple.

I answered my own question: No - it wouldn't work. SELECT
___gng() AS PRIM_KEY,;
___...

This will certainly work, but keep in mind that you will incur a significant performance overhead, generating and using PKs like this. Index files will be much, much larger (I would guess on the order of 50X, due to the way CDX files are compacted), meaning that joins & queries & inserts will be slowed, and generating PKs will probably be slower for any but the slowest network. I'm not saying it isn't an appropriate solution, but do consider it. :) -- Peter Crabtree

You *will* get duplicate values, using Sys(2015), even with just two users, eventually. It just happens. You need some way to *guarantee* uniqueness. Don't skimp here. It will bite you. -- Peter Crabtree
Ron and Peter:
I found this in VFP6's help file...

The name that SYS(2015) returns is created from the system date and system time. Calling SYS(2015) more than once during the same millisecond interval will return a unique character string.

Is that a typo? ..."during the same milisecond...will return a unique..." did they leave out the word " not "?

Sys(3) shows this warning:

SYS(3) may return a non-unique name when issued successionally on a fast computer. Use SUBSTR(SYS(2015), 3, 10) to create unique, legal eight character file name.

Help for SYS(2015) does not give that kind of warning
I use a combination of julian date anchored at 1900 + time passed midnight ( to the millisecond ). Convert these values to base62 for shorter field width. The tie breaker is, when each machine logs into the application it is asssigned a unique ID from an ID pool. The field is c(10), 2
characters for the machine ID and 8 for the date-time ID. This can accommodates 218 trillion ID's for about 3800 machines. I have applications running 10 years+ with 5 - 15 users with no problems. And, I have never had to reset a counter table. Doesn't mean it's the best, but it works. -- Ron Olson
Ron - Thank you for taking the time to post your solution and going into detail on how to make it work. Your response is much appreciated. I think I'm going to go with Alex's solution due to my needing something super simple. I have read your post several times and I have a pretty good handle on how it works. Your solution will generate a quantity of id's that no one can shake a stick at!
GUIDs are pretty good. Try to get the 16 char version. Also look at this: http://www.informit.com/articles/article.asp?p=25862&rl=1 -- Mike Yearwood
The code below may work some faster than the one using Create Object("scriptlet.typelib")
DO declare

LOCAL cGUID
cGUID = REPLICATE(CHR(0), 16)  && 128 bits

IF CoCreateGuid(@cGUID) = 0
	? StringFromGUID(cGUID)
ENDIF
* end of main

FUNCTION StringFromGUID(cGUID)
	LOCAL cBuffer, nBufsize
	nBufsize=128
	cBuffer = REPLICATE(CHR(0), nBufsize*2)
	= StringFromGUID2(cGUID, @cBuffer, nBufsize)
	cBuffer = SUBSTR(cBuffer, 1, AT(CHR(0)+CHR(0), cBuffer))
RETURN STRCONV(cBuffer, 6)

PROCEDURE declare
	DECLARE INTEGER CoCreateGuid IN ole32 STRING @pguid

	DECLARE INTEGER StringFromGUID2 IN ole32;
		STRING rguid, STRING @lpsz, INTEGER cchMax
-- Anatoliy Mogylevets
( Topic last updated: 2007.03.08 09:39:37 AM )