Wow, thanks for the comprehensive reply -- very good reasons indeed.

The downside I see from using this methodology is checking for duplicate "business" keys during an insert or update. With SQL Server, I add a UNIQUE CONSTRAINT to the table on the "business" fields for this purpose and use the INTEGER IDENTITY for the PK , but have not tried it for VFP, although I guess you could use candidate keys.

In the past I have added pre-insert/update code in FP to check for duplicates.

How do you handle this?

Also, how would you handle the extreme situation where possibly this key field became corrupted somehow. How could you rebuild the FK relation if the business data was not used as the PK of the other table. Guess it would be a tough situation either way.

Just Curious,
Mike

>Mike,
>
>
>I use integer surrogates for the following reasons.
>
>1) small - only take up 4 bytes and give me over 2 billion keys (more than any of my apps will ever use)
>2) easy to generate - always just "the next number" ... no fancy algorithm to use
>3) being a number , they are easy to increment in my "keys" table
>4) they are MUCH faster than character keys for joins - this was demonstrated beyond doubt in FoxPro Advisor a year or so ago.
>5) with surrogate keys, I never have to worry about needing to change the key. Even if I have another candidate key, such as an ID Code, I still use a surrogate key. This allows me to change the ID Code if I need to without having to change fkeys everywhere. My experience has definitely taught me that if you have any field with any business meaning at all, some user is gonna wanna change it at some point!
>6) It plays well with the naming convention I use (see previous post), though this is a side-effect and not a primary reason.
>7) Although I have not formally tested, I believe it also speeds up SQL updates from my Views - whose wheretype is "1" (prim key only)
>
>There are those who promote natural keys, but that either leads to:
>1) using fields with "business meaning" as keys and/or ...
>2) having to create compound keys as pkeys
>
>I stay as far away as possible from those approaches
>
>Hope this helps explain and is what you were asking for.
>Ken
>
>
>>PMJI,
>>
>>In a previous post you mentioned that you always use integer as surrogates for you primary keys. I am curious about this. I use them alot (especially with VFP/SQL Server apps) but not all the time. Could you please explain your experiences further?
>>
>>Thanks!
>>Mike