CODEWITHSUNDEEP
sqlsql-serverdatabaseforeign-keys
mark smith
mark smith

Reputation: 20907

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

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?

Upvotes: 1

Views: 1832

Answers (3)

Peter Perháč
Peter Perháč

Reputation: 20792

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).

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135181

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

Upvotes: 5

eflorico
eflorico

Reputation: 3629

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?

Upvotes: 3

Related Questions