[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-05-07
11
Medium Priority
?
477 Views
Last Modified: 2012-06-27
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 ?

0
Comment
Question by:Varshini
11 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35714175
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
 
LVL 9

Expert Comment

by:kaminda
ID: 35714437
You can use sp_foreachtable to execute a query against all the tables in the database
0
 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 1000 total points
ID: 35717805
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

Author Comment

by:Varshini
ID: 35821326
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35822517
Varshini,

Kindly let me know whether you have tried my query provided earlier which will work fine..
0
 

Author Comment

by:Varshini
ID: 35830738
no, the query did not work for me earlier too
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35833387
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
 

Author Comment

by:Varshini
ID: 35957996
i could not able to execute the query ....
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35959827
Are you obtaining any errors while executing the query..
Kindly explain what difficulty you are facing so that we can guide you accordingly..
0
 

Author Comment

by:Varshini
ID: 36227766
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
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 1000 total points
ID: 36228130
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question