How can i update all table relationships in one go?

Hello experts,
Assume I have created 3 tables in MS SQL 2000:
table A
* a_id (PK)
* name
table B
* b_id (PK)
* a_id
* name
table C
* c_id (PK)
* a_id
* name
Both table B and C have a cascade delete relationship with table A.
Question, now I want to cancel all cascade delete relationships in table
A, how can I do this in just one go? I dont want to go to each
relationship in table A and uncheck the "cascade delete related records"
option.
Thanks,
Benny
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Benny,
You need to write a script that will drop the FK constraint and create a
new one. e.g. something like this from Jacco Schalkwijk:
BEGIN TRAN
ALTER TABLE <table name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> FOREIGN KEY
(<column names>) REFERENCES <other table name> (<column names>) ON DELETE NO
ACTION
COMMIT TRAN
and reverse it with the same with ON DELETE CASCADE when you want to
enable cascading again.
The transaction is there to prevent other users from accessing the table
while you change the foreign key.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Benny wrote:
> Hello experts,
> Assume I have created 3 tables in MS SQL 2000:
> table A
> * a_id (PK)
> * name
> table B
> * b_id (PK)
> * a_id
> * name
> table C
> * c_id (PK)
> * a_id
> * name
> Both table B and C have a cascade delete relationship with table A.
> Question, now I want to cancel all cascade delete relationships in table
> A, how can I do this in just one go? I dont want to go to each
> relationship in table A and uncheck the "cascade delete related records"
> option.
>
> Thanks,
> Benny
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!