Hi mark,

>Sorry to drag back such an old thread but I remembered what you had said about using intelligent keys, (filtered on DELETED()).

>I never did write that update function I discussed during the thread and so I am now faced with the same situation as before.

>I need to do an append into a table from another table and I would like to keep the PKs in the source and the target tables the same. This time I have the same PKs in both tables (if the PKs exist at all)

>Of course if I do a straight append I will get an update conflict even if I have deleted the records in the target file.

Can you refer me to the thread, just to get more info about the problem ? Migrating from surrogates to intelligent or otherwise is not easy: you'll have to do an conversion. One question that arises is, why do you want to append one table to another. What is the story behind that one ?

>Could you point me a discussion of intelligent keys or expand on the idea some more for me.

Only very recent a heavy discussion was held about this very same topic. Thread #455216 and Thread #457550. warning, they're long winded and take a lot of time to get the info you need.

>Did I see some threads that advised against using them or am I imagining it:)

In short: Both strategies have advantages and disadvantages. Whether to use one over the other or even both is enterly your decission. In most cases, using surrogate keys are somewhat easier to manage, but are no miracle solution to every problem. Depending on your case you might be better off with intelligent keys in terms of performance (surrogate keys in general require more joins in queries) and readability of your table (with intelligent keys, every column has a meaning which makes ad-hoc querying and debugging a bit easier). OTOH when using surrogate keys you can prevent the trouble composite keys (keys consists out of more than one column) bring.

In the referred threads, I did argue that whether to use intelligent keys or surrogate keys might depend on your situation and it might be good practise to decide which to use on a case by case basis. Some agreed to this standpoint, others (who are in the always use surrogate keys camp) disagreed. If you read those threads you might find the information you need. If not, you can ask me or others.

>Thanks
>
>>Mark,
>>
>>Yes, You've got a problem here. Apparently this is one disadvantage of using surrogate keys, and you've got to find a workarround. Instead of deleting, you might try updateing the records.
>>
>>You would not have this problem if you were using intelligent keys, (filtered on DELETED()).
>>
>>Walter,
>>
>>
>>
>>>I have been using integer surrogate primary keys for a while now but sometimes I still wonder if I have the right idea.
>>>
>>>In the old days if I wanted to replace an entire set of parent records with a new set from a different source, all I had to do was delete them and append in the new set (with safeguards of course).
>>>
>>>Now I can no longer do this because my child tables still refer to the parents old primary key (my nextkey() routine having created new keys automatically)
>>>
>>>So nowadays to do the same job, I find I am having to write routines that update the existing set of records. This involves.
>>>
>>>Comparing old record set with new record set looking for deletions.
>>>Comparing old record set with new record set looking for Additions.
>>>Comparing old record set with new record set looking for changes to the records.
>>>
>>>and of couse wrting code to make any changes to the old record set that may be required.
>>>
>>>Of course of in a perfect world both my old set and my new set would both have the same primary key because they both derived from a single original source.
>>>
>>>At present this is not the case for the data environment I am in and I am getting fed up with coding for this situation.
>>>
>>>My questions are.
>>>
>>>1. Does it have to be this awkward or have I made some fundemental mistake in the way I am using surrogate keys.
>>>
>>>2. If so..... does anyone have a nice solid function that will do all the checking and updating I mention above:) I am sure I have written this before and lost it sometime ago.
>>>
>>>
>>>Muchos danke mes ami