views:

198

answers:

5

I need to change the values of a PK/FK (add 10000) on 2 tables. How do I tell the two tables involved that they should not care about referential integrity during the update, but to care after. I don't want to have to drop and recreate the relationships if I don’t have to.

A: 

Sorry, you have to. No option.

David M
+2  A: 

Your FK should have a "ON UPDATE CASCADE" option.

ALTER TABLE child CHANGE myfkconst FOREIGN KEY id REFERENCES parent (id) ON UPDATE CASCADE;

(or something like that. not 100% sure about the syntax)

And then you can just do

UPDATE parent SET id = id + 10000 WHERE id = something

and the child table will be updated automatically.

Tor Valamo
+2  A: 

You may want to disable all the constraints in the database by executing the following command:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all";

Then switching them back on with:

EXEC sp_msforeachtable @command1="print '?'", 
                       @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all";

Source: Stack Overflow - Can foreign key constraints be temporarily disabled using TSQL?

Daniel Vassallo
Does this also allow the IDENTITY column value to be changed?
Codesleuth
That might be a bit more complicated. You can check this regarding identities: http://www.mssqltips.com/tip.asp?tip=1397
Daniel Vassallo
+2  A: 

This link describes how to temporarily disable a contraint. I have not tested it.

-- disable constraint
ALTER TABLE table_name NOCHECK CONSTRAINT constraint_name
-- enable constraint
ALTER TABLE table_name CHECK CONSTRAINT constraint_name
rosscj2533
You need to be aware that with this method, when you turn the constraints back on, the DB will do a data integrity check. Your data may fail if there is something wrong, and fixing it can be problematic. This is also explained here: http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-tsql/159064#159064.
Daniel Vassallo
Yes, that's true, but this seems to be a simple case where the OP just needs to update each value by 10000, so this shouldn't be a problem. It seems the only answer that wouldn't have this problem would be changing the foreign key to be 'ON UPDATE CASCADE', which would change the foreign key rather than just disable it (which may be alright).
rosscj2533
A: 

You may find this link useful. It has a useful script for dropping all constraints on a table as well as a follow up script below to drop al FK constraint on a table

[T-SQL] Drop all constraints on a table

kevchadders