Link to home
Start Free TrialLog in
Avatar of Varshini
Varshini

asked on

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 ?

Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

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.
You can use sp_foreachtable to execute a query against all the tables in the database
ASKER CERTIFIED SOLUTION
Avatar of Alpesh Patel
Alpesh Patel
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Varshini
Varshini

ASKER

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.
Varshini,

Kindly let me know whether you have tried my query provided earlier which will work fine..
no, the query did not work for me earlier too
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..
i could not able to execute the query ....
Are you obtaining any errors while executing the query..
Kindly explain what difficulty you are facing so that we can guide you accordingly..
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial