Updating an object's primary key

frankhoffy posted on Thursday, February 08, 2007

I have an object whose underlying record is a composite key.  It is possible that the primary key value can be updated.  So far I have gotten around this by using extra variables to track the old values every time the key value properties are set.  I was wondering if there was a better way I can do this, perhaps by tying into n-level undo.  Any suggestions?

skagen00 replied on Thursday, February 08, 2007

If your primary key can be changed on a record, I would suggest using a surrogate key myself. I know there's a fair debate on using surrogate keys, but using a surrogate key will allow you to change the value of your "primary key" freely without affecting relational data.

Put a unique index on it if you want to ensure that it'll stay unique.

Not an easy thing to change if you're far along down the line, necessarily. But your primary key shouldn't really be changing after creation (or minimally shouldn't change often) IMO.

 

 

frankhoffy replied on Friday, February 09, 2007

skagen00:

If your primary key can be changed on a record, I would suggest using a surrogate key myself.

Tell that to my DBA

ajj3085 replied on Friday, February 09, 2007

Well then I guess your dba will create the necessary logic to update all the references, right?

GeorgeG replied on Friday, February 09, 2007

This is what I am doing on my end. Create a property called UniqueKey.
On Fetch assign 
    UniqueKey = various composites values coming from db
On the insert and just after the sql cmd
    UniqueKey = various composites values
On the Update 
    update cmd and use in where clause the
 various composites coulmns = UniqueKey
 assign again after the sql update to the latest values in case any of various composites values
 UniqueKey = various composites values

In addition, the SQL table should have a unique index key as the other users pointed out to have good valid data. Otherwise you will have to check every time you do an insert and update so you do not have dup ukeys.

Hope it helps

xal replied on Friday, February 09, 2007

frankhoffy:

Tell that to my DBA

Well if your dba is a good dba, then he should know that updating primary keys is not a good idea, specially if it conforms a clustered index. There's a good article on clustered indexes that Kimberly Tripp wrote recently in her blog....

I hope this doesn't turn into another of those kilometric discussions on surrogate keys or guid vs identity. We've had a fair share of those...

Andrés

Bowman74 replied on Friday, February 09, 2007

<quote>
Tell that to my DBA
</quote>

Ouch.  Informationless keys are your friend.  Did he give you any justification behind his decision?

Thanks,

Kevin