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 ?
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 ?
You can use sp_foreachtable to execute a query against all the tables in the database
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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..
Kindly let me know whether you have tried my query provided earlier which will work fine..
ASKER
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..
Later you can recreate all foreign keys ( you would be already having the Create scripts) with UPDATE CASCADE option..
ASKER
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..
Kindly explain what difficulty you are facing so that we can guide you accordingly..
ASKER
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.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_c
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
Once done, create Foreign keys with UPDATE CASCADE option as required.