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 |
|
objkiran
Starting Member
11 Posts |
 Posted - 2005-07-21 : 13:24:28
|
| I have two (2) tables.Table1 PK is Table2 FK,I have 100 rows in Table1, and 200 rows.Question: I have to change the value of one primary key in table1, How to Change?When I change I get an Foreing key error, because the PK is in use in table2 so I am not able to update.Please advise how to do this. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-07-21 : 13:40:46
|
| Add the new primary key record (instead of updating it)Update the foreign keys to the new valueDelete the original pk rowBe One with the OptimizerTG |
 |
|
|
objkiran
Starting Member
11 Posts |
Posted - 2005-07-21 : 14:26:39
|
| Presently I am doing that, but I dont feel comfortable doing that. I dont think doing this way is professional, there should be another way to do. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-07-21 : 14:40:36
|
| Your business rules and database design shouldn't be such that you need to change the value(s) of a primary key. Is this a one time thing or something you need to do regularly?Be One with the OptimizerTG |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-07-21 : 22:29:07
|
| if you don't have referential integrity set yet andif you need to do this repeatedly, you can explore cascade update,otherwise if it's one record only...update table2 FK, then update PK in table1, just remember to backup first or atleast copy the records in a staging table(s)--------------------keeping it simple... |
|
|
|
objkiran
Starting Member
11 Posts |
Posted - 2005-07-22 : 10:58:13
|
| This may be one time issue. When I am tring to merge data from two different servers located one in east cost and one in west cost. Human error caused someone to enter the same primary key here and there (which should not happen, as all primary keys are generated at east then pushed to west). So the IDs are different on both servers. Now when time came to merge IDs are different and data will be wrong if i merge because of the Ids. |
|
|
|
|
|
|
PK and FK Question