>>Bret,
>>
>>There are a number of reasons to NOT use meaningful primary keys, among them;
>>
>>1) If the primary key has meaning someone will want to change its value. When the vlaue of the primary key changes it is then necessary to update every record in every table that is related the one that has changed.
>>
>>2) One of the principles of relational database design is NO REDUNDANT DATA. If, for example, you have a customer table with a field named State that holds the state and a field named CustKey that has. soemwhere iside of it, the state for the customer, you have the state in two places and this is redundant data. Once you have redundant data you have the possibility of data inconsistency, where the value in the State field is not the same as the value burried iside of the CustKey field. This would be a problem especially in your case because you are using the compound key to order the records and the suer excepts the records to be in order by the descrete field values.
>>
>>3) Unless your compount key character field is 4 bytes or less an integer will take up less space, be easier to generate, and process faster during indexing and seeking (which is how relations are handled).
>>
>>4) The code to generate a unique integer key is smaller, simpler, and faster than the code to create a compound character key.
>>
>>5) An integer takes 4 bytes and can provide up tto more than 4,000,000,000 unique Ids, a character field using letters and digits would require 7 bytes or 75% more space. This becomes much more relevant when the record count starts climbing (for 1 million record we have 3MB of space extra required for the character field vs the integer field).
>>
>>There are other reasons but I think these are enough for now.
>
>1) My RI cascades changes to keys. Is the generated RI code unreliable?
>
>2) I don't like redundant data either. I could have made an index tag with expression genkey(field1,field2,field3) but I figured that such an ugly index would be really slow. I thought that making another field with the output of genkey would be worth the redundancy, since genkey is then evaluated only when adding or modifying a record, and the index expression is just keyfield. Even if I don't use this field as a key field, I would still want to have the field, rather than a complicated index expression. Genkey() takes a second or two to execute once and, even if its output was not used for unique keys, it could not be simplified very much. As you can see from the example, it mostly massages field2 to make field2 values be in the order the customer wants. If a complicated index expression is actually not a bad thing, that's news to me, and maybe contrary to VFP documentation, such as it is.
>
>3) If we accept 2), then adding a different field to use as a keyfield is using more space, even if not much more.
>
>4) I have to have genkey() anyway, whether or not I use it to write any field values.
>
>5) It's much the same as with 3).
>
>I'd like to hear about those other reasons you have, which are likely stronger reasons. Maybe they have to do with what happens when two users create identical records simultaneously, and the RI code misses it, or something. As I've said, I'm doing it this way partly because a large part of my work has been relating tables that used to be separate. I needed to make these keys in order to get the tables related in the first place. I also have a fourth table to be hitched up in the near future. Once I have it all mixed together, I can make that incrementing keyvalue table that everyone else uses, but I'm still keen to know why I need to, given the circumstances I've described. Thanks for the advice. I could still use some more.

Bret, first feel free to ASSUME you're smarter than I am. You may even be correct :-) As you thought, I AM a strong believer in unique but non-meaningful keys. In the 5 points above, your reasoning is certainly valid, but Jim & I put our emphasis slightly differently.

1. Sure referential integrity will cascade your changes. _IF_ all the records in question can get locks. _IF_ all the tables are available to the user. _IF_ you want to take the time. These risks are not acceptable to me in a multi-user environment.

2. Having your GenKey FIELD for indexing sure beats having a function in the index key. This is a place where redundant data is a good decision, particularly if your genkey field is not user-editable. However, that is a separate matter from this field being the Primary Key field. And is the genkey() function built into the RI system so that a user can't change a value with a VFP browse and not have the genkey field value updated?

3, 4 & 5 are a function of table and key structure. These are of primary importance only if you're using multi-million record tables or are watching table size closely for other reasons.

To me, item 1 is so much MORE important than the others that I make it a company guideline.

Hope all this ranting and raving makes sense....

Barbara