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 |
|
CanadaDBA
583 Posts |
 Posted - 2004-07-23 : 12:23:49
|
| I have two tables (T1 and T2) which have a relation on a column. If I want to update T1 then I would get the error because of FK in T2. UPDATE statement conflicted with COLUMN FOREIGN KEY constraint...What's the solution?Thanks, |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-23 : 12:48:57
|
| Assuming the problem is on the PK of T2:Create a new row in T2 with the new PK, change the T1 table, move all the data ffrom the old T2 row to the new T2 record, delete the old T2 recordBit of a bummer.Kristen |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-23 : 12:50:22
|
| You have to update the child rows first, then the parent ones. If you are updating the primary keys, well that's considered bad design to begin with.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-23 : 13:03:49
|
quote:
Originally posted by tduggan
If you are updating the primary keys, well that's considered bad design to begin with.
You trying to start a war again?Anyway, I agree with Tara...BUTIf they are PK's, look up Cascading UPDATES and DELETES on the CREAT TABLE syntax in BOL (Books Online)This is SQL Server, right?EDIT: and since I re-read (OK, so it's the first time I read it) your post, it's says you had trouble with the FK, which means you are trying to change a child key to something that does not exists in the Parent.Which means, you need to add it to the parent then perform the update...there is no reverse cascading...what's the opposit of cascading?Brett8-) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-23 : 13:41:26
|
| So this leads to the "appropriate" solution being:DROP FOREIGN KEYMake ChangeFiddle with data so that you can:CREATE FOREIGN KEYNo seriously, might be the easiest way if its a one-off job and completes complete with chicken-and-egg.Kristen |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-07-23 : 14:18:35
|
| These give me an idea how to handle the problem. So, I have to do a lot of work. The cascade wouldn't harm my data in this case. Some Branch numbers should be change in entire database. I thought we have something like SET CASCADE ON and then after did the work, set it OFF. But seems I was wrong. Thanks,Canada DBA |
|
|
|
|
|
|
|
|
Update related tables