Update Primary Key value

  • Thread starter Thread starter runner
  • Start date Start date
R

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 said:
I have a problem when updating the value of a primary key column in the
parent table.
Click to expand...

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?
 
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??
 
runner said:
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??
Click to expand...

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...
 
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
 
Scott said:
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)?
Click to expand...

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.)
 
Back
Top