>>People have advocated using random or incremented numbers as primary keys, having no meaningful property except uniqueness. It is not clear to me why the primary key should not also be used for other purposes, such as keeping tables in the correct order, or relating two tables that used to be separate.
>>
>>In my case, I have been given tables that were developed in different places at different times by different people. These tables had no actual keyfield. I had to create a function which would read in three or four fields and generate a complex alphanumeric field which, sorted alphabetically, would order the records the way they wanted. The values this function generated for each record were unique, but also based on the actual data. I was therefore able to use these keys to relate three tables that had been completely separate. Since my applications were originally intended for standalone use and had no views, I felt free to make the keys any way I wanted. Also, the tables were only a MB or two, and I was free to modify the formats as I saw fit, as long as the reports printed correctly.
>>
>>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?
>>
>>As Mr. Nedeljkovic would say, "Sorry for the sheet."
>
>Can you pls give some example or sample data and how you are generating that special alphanumeric field..

Certainly. This is approximate:

field1 - Two or three possible values: 74-75 or 76.
field2 - values such as 13, 121.1, 28B, 30C.1, 488AAY, 50.1C.
field3 - usually 0, sometimes 1, could possibly be higher.

These three fields uniquely define a record in all my parent tables. I use a function called genkey, which reads these and makes a value which I put in keyfield. Genkey has about a page of code, which I can give you if you really want it, but the important thing is that, for instance, if field1 = '74-75', field2 = '267BA.2', and field3 = 1, then genkey(field1,field2,field3) = '750267BA--0002----1'. The main point of formatting the keyfield this way is that field2 is mainly what people look at in the reports. It has to be alphanumeric, but '9' has to come before '10', 28AA comes before 28B, 188.9 comes before 188.10, and so forth. My reports look like they are ordered 'intelligently' on field2, but they are really ordered on keyfield.

When I was given table2 and table3, to be related to table1, I had to actually relate them on these three fields, which existed in all three tables (more or less). By using genkey to make these keys for the other two tables, I was able to relate them all, and identify orphans, which were then put in a list for the client to ponder.

So I have all these keys, which are both unique and useful for ordering. When a record is added, I get the values from the textboxes, make a key with genkey(), and INSERT INTO tablewhatever(keyfield, field1, etc.) VALUES newkey, fieldval, etc. I'm content to go on doing that, but some people here (I believe Barbara Paltiel was one) have said that primary keys should only be used for their uniqueness, and should be generated by incrementation or random methods. I didn't understand their reasons, and I didn't want to ASSUME, though I'm free to SUPPOSE, that I'm smarter than everybody else.