John J. Hughes II <no@invalid.com> wrote:
Hum, whereas I agree with the sentiment it's a little simplistic.
I do try to avoid changing primary keys but short of having identities in
all my tables I don't see a practical way of doing this all the time. My
silly customer keep insisting that there be an option to change the keys.
I don't see what's wrong with having an identity in every table. Other
than the very slight hit of having an extra column, it seems to solve
the problem very neatly. You could then have additional uniqueness
constraints on other columns if you wanted to.
A key is what makes a row unique but if I want change the key the row would
still be unique but unique in a different way.
No, a key is more than what makes a row unique - it's what allows it to
be identified. This is very important if you ever have a situation
where one client fetches a row and then later wants to modify it. If
another client has come in in the mean time and changed the primary
key, there's no way of the first client identifying it any more.
Since the dataset retains
the original row data and the changed data it's perfectly capable of
updating the SQL with no problem (or at least I have never found one). The
problem is when the select statement is run by the update to get the changed
rows. Now the updated dataset no longer retains the old key values so it
can not merge back to the old dataset which still knows the orginal keys.
It sounds like you *might* be okay just by writing the SQL commands
yourself and specifying that the parameters in the where clause should
use the original values rather than the current (ie new) ones.
Now I have a theory that if I do a GetChanges and Update (which works) and
then do an AcceptChanges on the old dataset before doing the merge I might
solve my problem. Of course I then have to do an AcceptChanges after the
merge for a total of two AcceptChanges. But since I am not very familiar
with how the Merge function works I am concerned with implementing this and
hoping for the best.
I don't know about Merge myself, to be honest. Calling AcceptChanges is
rarely a good idea though, in my experience.
I still *strongly* recommend changing the schema so that you've got a
primary key which *doesn't* change.
--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too