Changing a record in a table (sql server) that has foreign keys?

Question

Hi there,

Does anyone know if there is a quicker way of editing a record that has foreign keys in a table (in sql server).. i will explain.. i have approx 5 tables that have there own ID but are linked together using a foreign key...

Hence i needed to change the foreign key (the contract number in my case), but i had to copy each record to a new record and edit it that way...

As if i try to edit the contract number it gives me the standard error of being associated and violates a foreign key etc

Surly there must be a better way?

ANy ideas?

Answer 1

I'm not an SQL expert, but can't you set something like ON UPDATE CASCADE to automatically update the foreign key when the primary key is changed?

Answer 2

are you talking about changing the PK and then updating all the Fks? In that case enable cascade updates and this will be done automagically same with deletes, you enable cascade deletes

ON DELETE CASCADE

Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the rows deleted from those tables.

ON UPDATE CASCADE

Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all of the foreign key values are also updated to the new value specified for the key. If cascading referential actions

Answer 3

Or try disabling the integrity constraint, do your changes and attempt to re-enable the constraint. Basically, if you didn't do it right you will get an error then (can't enable a constraint that would be violated).

Answer 4

thanks everyone for there comments, is ON UPDATE CASCADE considered a bad thing or a good thing???

OND DELETE CASCADE I can see why this could be dangerous... but ON UPDATE CASCADE - is the general form that people have this turned on or off...

Any advise appreciated