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.
+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
2010-01-15 16:16:36
+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
2010-01-15 16:19:49
Does this also allow the IDENTITY column value to be changed?
Codesleuth
2010-01-15 16:21:45
That might be a bit more complicated. You can check this regarding identities: http://www.mssqltips.com/tip.asp?tip=1397
Daniel Vassallo
2010-01-15 17:03:29
+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
2010-01-15 16:21:05
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
2010-01-15 16:44:08
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
2010-01-15 16:52:39
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
kevchadders
2010-01-15 16:21:54