How do i change the cascade rule for FK in all tables in the database ?

i have company id is pk in my database and it referd as FK in  more than 200 table. So i need to change cascade update rule for FK in all my tables.

ALTER TABLE  employeeAddress  WITH CHECK ADD  CONSTRAINT [FK_companyid] FOREIGN KEY([companyid])
REFERENCES [dbo].[company] ([companyid])
ON UPDATE CASCADE

How do i  SEARCH and replace the casecase rule for the column in my db using sql query ?

VarshiniAsked:
Who is Participating?
 
Alpesh PatelConnect With a Mentor Assistant ConsultantCommented:
SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME +
' WITH NOCHECK ADD  CONSTRAINT  ' + CONSTRAINT_NAME +
' FOREIGN KEY(' + (Select Top 1 COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE CONSTRAINT_NAME = information_schema.table_constraints.CONSTRAINT_NAME) + ')' +
' REFERENCES ' + TABLE_SCHEMA + '.' +
(Select OBJECT_NAME(rkeyid) from sys.sysreferences WHERE OBJECT_NAME(constid) = information_schema.table_constraints.CONSTRAINT_NAME) +
'(' + (SELECT
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1 AND OBJECT_NAME(ic.OBJECT_ID) = (Select OBJECT_NAME(rkeyid) from sys.sysreferences WHERE OBJECT_NAME(constid) = information_schema.table_constraints.CONSTRAINT_NAME)) + ')' +
' ON UPDATE CASCADE ' +
' ON DELETE CASCADE '
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
You need to manually drop all those Foreign keys and recreate it with UPDATE CASCADE option to get it work.
Hope you would be having Create Scripts for all Foreign keys and if so, you can drop all foreign key constraints using the script below:

SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME +
' DROP CONSTRAINT ' + CONSTRAINT_NAME
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

Once done, create Foreign keys with UPDATE CASCADE option as required.
0
 
kamindaCommented:
You can use sp_foreachtable to execute a query against all the tables in the database
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
VarshiniAuthor Commented:
PatelAlpesh:

i got the following error when i execute the above script

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Varshini,

Kindly let me know whether you have tried my query provided earlier which will work fine..
0
 
VarshiniAuthor Commented:
no, the query did not work for me earlier too
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
you need to execute the result of that query 3 to 4 times to get all your foreign keys dropped.
Later you can recreate all foreign keys ( you would be already having the Create scripts) with UPDATE CASCADE option..
0
 
VarshiniAuthor Commented:
i could not able to execute the query ....
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Are you obtaining any errors while executing the query..
Kindly explain what difficulty you are facing so that we can guide you accordingly..
0
 
VarshiniAuthor Commented:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
0
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
Try out the queries here..
http://www.siusic.com/wphchen/how-to-script-out-all-the-foreign-keys-of-a-table-106.html

First script to drop all constraints
Second one to create all constraints back - Just add CASCADE rules at the end to get it work..

Note that it will not work if you have created foreign keys on multiple columns..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.