Your problem goes to the root of an issue of abstraction. A data table is an abstraction of some class. Each record represents an instance of the class and each field some property or attribute of the class instance, right? However, the table itself has a level of abstraction as well -- i.e., a table could be a part of a class called DataStorageUnit with properties such as record and field[n,x] (where n = field number and x = some kind of description). So, here is the issue as I see it.

When we create a PK (or CK for that matter), what are we attempting to uniquely identify?

If we are attempting to "fingerprint" the table and record then a SYS(2015) or SYS(3) or other unique key alone is sufficient to the purpose. Even if we are attempting to uniquely ID the actual subject of the table abstraction (i.e., customer or invoice), these routines are plently. However, if the PK or CK pulls double-duty and contains property or attribute information about a particular instance then we have another problem completely.

Input from a user always holds some opportunity for duplication. Even if the source of that information has a carefully designed and managed unique key within it. The user can always have typos or misreadings or other potential violations of the key. So, we have to ask ourselves something:

Given that user information can be duplicated (either purposefully or otherwise), how do we want to deal with such a duplication?

There are a number of factors (in my mind) to consider. It is obvious that if we determine that a primary key is needed we MUST know when the key is violated. The central question is who or what handles the trapping of the error?

If we let FoxPro handle this error then we lose flexibility. Fox only has limited error trapping and handling capacity at a class level of abstraction. In my mind the best and most flexible solution is this (remember we are talking about primary keys that contain attribute information of a class abstraction instance):

1. Create your instance PK with whatever information available as you choose.
2. Prefix or suffix the key with a computer generated key (this will prevent Fox from generating a system level PK violation).
3. Make a base class method (in your framework) that checks for duplicate PK/CK information (less the computer generated key prefix or suffix).
4. Create a PK/CK violation handling class that will handle common issues and (as a class) can be expanded to include system specific issues as you have need.

I can tell you that one thing I dislike is the use of PK's pulling double duty as both a record level abstraction PK and a user (or class function) readable serial number style PK used informationally in a system. Invariably, the two abstract concepts end up causing problems in one way or another for each other. If you want serial numbers for your class abstractions, then create serial numbers. If you want each record easily and uniquely identified, then do so at a level where the user is completely unaware that this is happening. However, do NOT use class abstraction information (i.e., first name+last name+middle initial+date of birth) to function in the role of uniquely IDing a table record. Even the record number is a poor way to do this. Instead, make you PK field your record ID stamp and then create CK's as you need them to ID the class instance.

Purest OOP is violated a number of ways by having class properties that pull double-duty. One, is the concept of self-documentation. When subsequent programmers look at your work they must decide in each usage of the PK whether the property is being used to ID the record or an instance of the table class. Perhaps it is obvious to them, perhaps not. If they guess wrong then they may create problems there and elsewhere. If you have your PK's as ALWAYS being a "fingerprint" of the record with CK's being gen'd for specific purposes and document this clearly in the DBC comments section, it is likely that other programmers (or even yourself) months later will always know what you intended and how you implemented the use of your table structures in your programs.

It violates Direct Mapping, which states that classes and class properties have only one representation and use.

I think you get the idea.

Bottom line -- use the table PK as a record level abstraction. Create CK's as you require them to ID entity class instances and tag them with computer generated keys to keep them 100% unique. Never use one field (class property) to pull double abstraction duty in your data sets. Use your own PK and CK error handling at a class level. Finally, let each class know about each of its keys, but do not make the record level key available to the user (only the system -- and even then, don't spread the knowledge far and wide).

Hope this helps

Larry