Update Primary Key value

[runner]

Hello!

I have a problem when updating the value of a primary key column in the
parent table. After the update which works fine against the database and all
its childrows - I get a constraint error when doing merge on the dataset.

Can someone explain why the update against the database works fine but when
merging the dataset I get a contraint error??

I believe it has to do with Current and Original value but I can´t seem to
find any answers - only to the problems with autoincrementing values.

Thx

 

[runner]

Hello, again!

I update the pk-value manually in my app

 

[Jon Skeet [C# MVP]]

I have a problem when updating the value of a primary key column in the
parent table.

That's a problem to start with. Updating a primary key is fundamentally
a bad idea. The idea of a primary key is that it can be used to
identify the row uniquely - for a row to change its very identity is
nasty, as everything which uses the primary key as a foreign key from a
different table has to be updated simultaneously - and everything in
the dataset needs to understand that, too.

Is it too late to change your design so that the primary key doesn't
change?

 

[runner]

Hello, no - I suppose I can change the design slightly - but I had a
"natural" primary key column that always was unique. It could on the other
hand get changed.

But can you explain why the everything works fine against the db and in the
dataset I get a constraint error and it adds a new post instead with the
same pk value??

 

[Jon Skeet [C# MVP]]

Hello, no - I suppose I can change the design slightly - but I had a
"natural" primary key column that always was unique. It could on the other
hand get changed.

But can you explain why the everything works fine against the db and in the
dataset I get a constraint error and it adds a new post instead with the
same pk value??

I'd have to look in detail, but basically I suspect it doesn't know
that you've updated an existing row - it thinks you've created another
row. After all, the only means it's got of identifying the row (the PK)
has changed...

 

[Scott]

Jon Skeet [C# MVP] wrote:

Jon,

Can you shed some more light on this statement? I'm envisioning a table
of cars that uses VIN as the PK. It's quite possible that such a number
could be enterened incorrectly and need to be updated. With the
exception of a surrogate PK, I could see that most any PK could need to
be updated. Are you implying that surrogates should always be used or
to delete and re-add any changes to the PK field(s)?

Thanks.
Scott

 

[Jon Skeet [C# MVP]]

Can you shed some more light on this statement? I'm envisioning a table
of cars that uses VIN as the PK. It's quite possible that such a number
could be enterened incorrectly and need to be updated. With the
exception of a surrogate PK, I could see that most any PK could need to
be updated. Are you implying that surrogates should always be used or
to delete and re-add any changes to the PK field(s)?

I would suggest using a PK which is definitely *not* user-entered,
which is auto-generated and which doesn't change. I would suggest it
should normally be used for nothing apart from being a key (and index,
of course). That way you can have very simple PKs, and you always know
that nothing "dangerous" is going to happen to them.

(It's much the same attitude I take towards locking in .NET - use an
object which isn't used for anything else.)