Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
TorreyKite
Starting Member
40 Posts |
 Posted - 2003-11-26 : 15:03:22
|
What is the standard procedure for updating the value of a PK when other tables have relationships with that PK?I do not want to orphan any of the records in the related tables. so should I update the foriegn key first in all related tables? Then update the PK? Is there a way I can do them all at once so as not to cause any errors with my constraints?Thanks in advance.Regards,TK  |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2003-11-26 : 15:14:43
|
| It is not usually a good idea to update a primary key.But, If you are using SQL Server 2000 One option would be to look into ON UPDATE CASCADE - Cascading Referential Integrity Constraints in BOL |
 |
|
|
TorreyKite
Starting Member
40 Posts |
Posted - 2003-11-26 : 16:32:15
|
| I'm using SQL Server 7...So I guess the best answer is that standard procedure plainly to design the DB so there is no need to update a PK. I had used "Cascading Referential Integrity Constraints" in MS Access ... and thought there might be a way to write an update query that would do that for me (for special circumstances).ThanksTK |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-11-27 : 05:31:45
|
| In SQL 7, the only way you can do what you need is to 'simulate it'....Create the NEW Parent, transfer all the CHILDREN to the new PARENT, and then delete the old PARENT....and if there are 'grandparent,parent,child' type relationships this gets harder....not impossible, just harder....but it can be made to work. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-11-27 : 07:29:20
|
| A PK is a record identifier and so should never be updated.An update to a PK shold be coded as a delete and insert.A need to update the PK is an indicate that the choice of PK is incorrect.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-11-27 : 17:05:28
|
| You know what I am going to say Nigel, so this is for the poster..A key is a unique attribute designed to identify the row in a table.Any update statement (regardless of the column) is simply a shortcut for a delete followed by an insert. The fact that SQL Server can do it to a column that is marked as a PK is a bonus.A need to update the PK should be considered a rare occurence. Frequent updates of the PK may indicate a poor design choice. DavidM"SQL-3 is an abomination.." |
|
|
|
|
|
|
UPDATE with PK Constraints