| Author |
Topic |
|
rasmasyean
Starting Member
22 Posts |
 Posted - 2009-11-03 : 11:31:42
|
| Is there a way to directly update a row’s primary key column(s)? Or do you have to select the row, delete the row and then insert it with the changes? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rasmasyean
Starting Member
22 Posts |
Posted - 2009-11-03 : 12:54:22
|
quote:
Originally posted by tkizer
There is nothing preventing you from updating the primary key. Updates are allowed. However you will run into issues if there are child rows via the foreign keys.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong."
Why do I get an InvalidOperationException"The property 'description' is part of the object's key information and cannot be modified."I'm using ADO.NET data services in WCF by the way. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rasmasyean
Starting Member
22 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-11-03 : 15:41:23
|
| HOWEVERI don't consider it a good practice to do so, and prevent my developers for requiring CASCADING anythingTo me a key is a keyIf for some reason, the great state of NJ (Mob Central) what's to change it's name..then that state ceased to exist, and there would be an end date of it's existance.....you would not want to associate our glorious past with what may happen with today's electionBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rasmasyean
Starting Member
22 Posts |
Posted - 2009-11-03 : 16:06:25
|
quote:
Originally posted by X002548
HOWEVERI don't consider it a good practice to do so, and prevent my developers for requiring CASCADING anythingTo me a key is a keyIf for some reason, the great state of NJ (Mob Central) what's to change it's name..then that state ceased to exist, and there would be an end date of it's existance.....you would not want to associate our glorious past with what may happen with today's electionBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam
??? What the heck is that supposed to mean? I never cared about NJ politics to have a clue what you are talking about!  |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-11-03 : 16:09:49
|
| Well you gotta update your profile so we know where you are fromAnd you don't, the mob could be running your industriesAlso, here in NJ, it's not a choice of the best politian, it's the choice of the best crooked politicianThe bad ones get caught, then there's a big mess, then you gotta re-elect someone....if you're not careful it could be a vicious cycleBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
rasmasyean
Starting Member
22 Posts |
Posted - 2009-11-03 : 20:26:50
|
quote:
Originally posted by X002548
HOWEVERI don't consider it a good practice to do so, and prevent my developers for requiring CASCADING anythingTo me a key is a keyIf for some reason, the great state of NJ (Mob Central) what's to change it's name..then that state ceased to exist, and there would be an end date of it's existance.....you would not want to associate our glorious past with what may happen with today's electionBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam
So...what if you don't have to cascade anything?What's wrong with updating a primary key? Data can't be permanent, can it? |
|
|
|
rasmasyean
Starting Member
22 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
rasmasyean
Starting Member
22 Posts |
Posted - 2009-11-04 : 09:13:46
|
quote:
Originally posted by X002548
quote:
Originally posted by rasmasyeanSo are you saying that the best way to do it is to Delete and Insert in a transaction with a rollback option?
Huh?New Sheriff in town here in JerseyBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam
How else are you going to change the keyed column without an UPDATE statement?I would guess that you have to copy the row to a buffer, modify the buffered row, DELETE the row, then INSERT the buffered row back into the table. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-11-04 : 12:26:11
|
quote:
Originally posted by rasmasyeanHow else are you going to change the keyed column without an UPDATE statement?I would guess that you have to copy the row to a buffer, modify the buffered row, DELETE the row, then INSERT the buffered row back into the table.
I think there is some confusion. Brett and Tara are saying that it is considered bad practice to update/change a primary key. And, depending on what you are doing, it points to a design issue. But, if you want to update the primary key you certainly can and Tara showed some code for how to do that. Now, if you are coding .NET and using datasets and such that you are changing and need to persist back the database, then it sounds like you might have an issue with your .NET code. I've not done any real .NET programming in the last 5 years so I'm pretty rusty, but I don't recall any limitation on being able to update a primary key. But, I also suspect that since the PK has changed .NET might be inclined to insert a new row instead up updating the existing row in that scenario..? |
|
|
|
rasmasyean
Starting Member
22 Posts |
Posted - 2009-11-06 : 23:20:37
|
quote:
Originally posted by Lamprey
quote:
Originally posted by rasmasyeanHow else are you going to change the keyed column without an UPDATE statement?I would guess that you have to copy the row to a buffer, modify the buffered row, DELETE the row, then INSERT the buffered row back into the table.
I think there is some confusion. Brett and Tara are saying that it is considered bad practice to update/change a primary key. And, depending on what you are doing, it points to a design issue. But, if you want to update the primary key you certainly can and Tara showed some code for how to do that. Now, if you are coding .NET and using datasets and such that you are changing and need to persist back the database, then it sounds like you might have an issue with your .NET code. I've not done any real .NET programming in the last 5 years so I'm pretty rusty, but I don't recall any limitation on being able to update a primary key. But, I also suspect that since the PK has changed .NET might be inclined to insert a new row instead up updating the existing row in that scenario..?
Why is it a design issue if you have to update a primary key field? What's wrong with doing that?Like if you have Name, Address in your PK ...and you want to change the name because it's was spelled wrong in the beginning. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-11-07 : 13:58:25
|
| In that situation you'd use a surrogate key for the primary key and would not have to update the PK to make that kind of data change. That's why it's a design issue. If you have to design into your system the ability to change the PK data, then you should instead consider a surrogate key such as an identity column.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-11-07 : 17:50:26
|
| Address tables where always the fly in the ointment for my stand against surrogate keys.I could never argue the feasibility of have a pk of Street Number, Street addr line1, street addr line2, City, County, State and zipI wouldn't win that oneIt's just that surrrogates are SO overused and abused it's just sillyHell Even M$ uses surrogates in it's catalog..causes ALL kinds of pain with users and loginsIn any case"Do as you will, Dr. Jones"we are only passing through timeBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
|
How to update a primary key column