>>In the near future, I will be adding a fourth table of pre-existing data and using a similar function to relate records to the ones I have. All of this will have to go client-server at some point. Then I will have to make sure that keys are generated properly as more records are added. Is there any compelling reason why I should not continue to use these meaningful keys?
>
>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.