Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Ken Murphy
  Where is Ken Murphy?
 Springhill
 Canada
 Ken Murphy
 To: Andy Kramek
  Where is Andy Kramek?
 Hot Springs Village
 Arkansas - United States
 Andy Kramek
 Tags
Subject: RE: Primary Key
Thread ID: 143107 Message ID: 143419 # Views: 38 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Tuesday, August 28, 2007 6:03:54 PM         
   


>
> As far as it goes. I would clarify three points:
>
> First:
> The non-sequential issue is not just one of searching - it has physical impacts in server databases that rely on clustered indexes. This is where the data in the table is PHYSICALLY ordered in the index sequence. While it is the most efficient type of index for retrieval, it is a pain when you need to insert a record 'between' existing records. (The process is similar to the old VFP SORT command - you copy all records out to a temporary table, add back those prior to the new one, then the new one, then the rest. NOT at all efficient and positively slow when tables are large or you are doing a lot of inserts).
> This is the reason that all databases have some form of sequential auto-generation for primary keys that ensures that new records are only ever added at the 'end' of the table.
>
> Second:
> I am not against using GUIDS where you need them - as I have already said. For merge/replication add a GUID by all means to uniquely identify records - but unless you also know where it came from that isn't much use. An equally valid solution is to generate a new (integer) PK and store the source table and old source table PK in the new record. The result is a three-column or 'poor-mans' GUID (NEW PK, Source + Old PK). But at least you can find the original record from it - which you can't if all you have is a GUID!
>
> Third:
> You mention running out of integers - well maybe in VFP that supports only 4-byte integers that is an issue (though if you are using VFP to store tens of millions of records I would have to question the wisdom of that decision) but in back end servers you can use 8-byte (+/-9,223,372,036,854,775,807) or even 16-bit Integers (which are actually UUIDs anyway). So that really isn't an issue.
>
> Finally I would also point out as a general principle that generating PK values on the client as opposed to the server is really very bad practice! The only way you can be truly certain that a generated key does not already exist, is to check all other extant keys. It follows, therefore, that the only reliable place to generate a primary key is in the database.
>
> Q.E.D
>
> Regards
> Andy Kramek
> Microsoft MVP (Visual FoxPro)
> Tightline Computers Inc, Akron Ohio, USA

Thanks Andy,

While I use SQL back ends, I am in no way an expert on SQL. The point you make regarding clustered indexes is something that I am going to have to research. (Not really sure what the difference between a clustered index and a non-clustered ??? index might be and what differences they might make to my app.)

Your point about long integers is quite understood. VFP only has a 4 byte int autoinc field, but in SQL, you can select a long or big int and set up the identifier properties. (For those following this thread who are unfamilliar with autoinc fields in anything other than VFP, in SQL you can make an integer field, or a long integer, etc. field automatically increment by setting the Identity property (in the SQL table designer) to true. An integer field uses 4 bytes and a long Integer field uses 8 bytes. You can store larger numbers in an 8 byte field than you can in a 4 byte field.)

Re-joining databases, I have to agree. There is no way to know where the original record came from unless you store that information some where. (I recently joined two databases and did it the same way that you suggest - I created a new Int based PK instead of using a GUID and included a reference table with three fields: NewID, OldTable & OldPK.)

Re where to create your PK, there have been times when I have thought "wouldn't it be great if I could generate this PK at the client" but then again, as you say, the only real way to find out if that PK is unique is to actually check and see if it is indeed unique. An Autoinc field is a LOT less work than generating a PK and then testing it to see if it is unique - especially if checking the uniqueness of the PK depends on if you are currently connected to the Internet. For those apps, I finally decided to assign a temporary local PK (with negative numbers) and when I send those records to the server, I apply the new "real" PK.

PS, looking forward to meeting you at SW Fox.

Ken
You shall know the truth - and the truth shall set you free. (John 8:33)

ENTIRE THREAD

Primary Key Posted by Aaron Hathaway @ 8/24/2007 10:25:32 PM
RE: Primary Key Posted by Borislav Borissov @ 8/24/2007 10:46:52 PM
RE: Primary Key Posted by Aaron Hathaway @ 8/24/2007 11:09:39 PM
RE: Primary Key Posted by Ken Murphy @ 8/25/2007 1:47:10 AM
RE: Primary Key Posted by Barbara Peisch @ 8/25/2007 5:01:14 AM
RE: Primary Key Posted by Mike Yearwood @ 8/26/2007 2:02:59 AM
RE: Primary Key Posted by Andy Kramek @ 8/27/2007 1:01:44 PM
RE: Primary Key Posted by Mike Yearwood @ 8/27/2007 3:37:30 PM
RE: Primary Key Posted by Andy Kramek @ 8/27/2007 5:57:08 PM
RE: Primary Key Posted by Mike Yearwood @ 8/27/2007 7:55:38 PM
RE: Primary Key Posted by Andy Kramek @ 8/27/2007 8:31:58 PM
RE: Primary Key Posted by Mike Yearwood @ 8/28/2007 2:33:15 PM
RE: Primary Key Posted by Andy Kramek @ 8/28/2007 2:48:25 PM
RE: Primary Key Posted by Ken Murphy @ 8/28/2007 3:48:57 PM
RE: Primary Key Posted by Andy Kramek @ 8/28/2007 5:06:25 PM
RE: Primary Key Posted by Ken Murphy @ 8/28/2007 6:03:54 PM
RE: Primary Key Posted by Andy Kramek @ 8/28/2007 9:10:24 PM
RE: Primary Key Posted by Ken Murphy @ 8/28/2007 10:48:19 PM
RE: Primary Key Posted by Andy Kramek @ 8/29/2007 12:41:37 PM
RE: Primary Key Posted by Ken Murphy @ 8/29/2007 12:58:20 PM
RE: Primary Key Posted by Mike Yearwood @ 8/29/2007 7:49:54 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/25/2016 7:09:30 PM
RE: Primary Key Posted by Tore Bleken @ 7/25/2016 8:03:30 PM
RE: Primary Key Posted by Hugo Ranea @ 8/29/2007 9:34:10 PM
RE: Primary Key Posted by Daniel Hofford @ 8/29/2007 11:46:07 PM
RE: Primary Key Posted by Mike Yearwood @ 7/24/2016 1:53:22 PM
RE: Primary Key Posted by Benny Thomas @ 7/24/2016 2:30:24 PM
RE: Primary Key Posted by Mike Yearwood @ 7/24/2016 3:20:23 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/25/2016 7:16:41 PM
RE: Primary Key Posted by Mike Yearwood @ 7/25/2016 8:49:05 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/25/2016 7:04:07 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/25/2016 6:59:58 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/25/2016 7:00:06 PM
RE: Primary Key Posted by Koen Piller @ 7/25/2016 11:37:27 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/26/2016 8:01:52 PM
RE: Primary Key Posted by Mike Yearwood @ 7/26/2016 8:59:17 PM
RE: Primary Key Posted by Gene Wirchenko @ 7/27/2016 7:02:39 PM
RE: Primary Key Posted by Paul Gibson @ 7/27/2016 12:21:25 PM