SQL Server Performance Forum – Threads Archive

Urgent: how disable a foreign key

is there a same solution in sql server 2000 like "alter table tablename disable primary key cascade" as in oracle? Yes? Can you give me the syntax please. Thank you very much for your help.

From BOL: Cascading Referential Integrity Constraints
Cascading referential integrity constraints allow you to define the actions MicrosoftÂ® SQL Serverâ„¢ 2000 takes when a user attempts to delete or update a key to which existing foreign keys point. The REFERENCES clauses of the CREATE TABLE and ALTER TABLE statements support ON DELETE and ON UPDATE clauses: [ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
NO ACTION is the default if ON DELETE or ON UPDATE is not specified. NO ACTION specifies the same behavior that occurs in earlier versions of SQL Server. ON DELETE NO ACTION Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE is rolled back. ON UPDATE NO ACTION Specifies that if an attempt is made to update a key value in a row whose key is referenced by foreign keys in existing rows in other tables, an error is raised and the UPDATE is rolled back. CASCADE allows deletions or updates of key values to cascade through the tables defined to have foreign key relationships that can be traced back to the table on which the modification is performed. CASCADE cannot be specified for any foreign keys or primary keys that have a timestamp column. ON DELETE CASCADE Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the rows deleted from those tables. ON UPDATE CASCADE Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all of the foreign key values are also updated to the new value specified for the key. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the key values updated in those tables. Examples of cascading referential actions can be based on the FK_Products_Suppliers constraint on the Products table in Northwind. This constraint establishes a foreign key relationship from the SupplierID column in the Products table to the SupplierID primary key column in the Suppliers table. If ON DELETE CASCADE is specified for the constraint, deleting the row in Suppliers where SupplierID equals 1 also deletes the three rows in Products where SupplierID equals 1. If ON UPDATE CASCADE is specified for the constraint, updating the SupplierID value in the Suppliers table from 1 through 55 also updates the SupplierID values in the three rows in Products whose SupplierID values currently equal 1. Cascading actions cannot be specified for a table that has an INSTEAD OF trigger. After a cascading action has been defined for a table, an INSTEAD OF trigger cannot be added to it. Multiple Cascading Actions
Individual DELETE or UPDATE statements can start a series of cascading referential actions. For example, a database contains three tables, TableA, TableB, and TableC. A foreign key in TableB is defined with ON DELETE CASCADE against the primary key in TableA. A foreign key in TableC is defined with ON DELETE CASCADE against the primary key in TableB. If a DELETE statement deletes rows in TableA, the operation also deletes any rows in TableB that have foreign keys matching the deleted primary keys in TableA, and then deletes any rows in TableC that have foreign keys that match the deleted primary keys in TableB. The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree containing no circular references. No table can appear more than once in the list of all cascading referential actions that result from the DELETE or UPDATE. The tree of cascading referential actions must not have more than one path to any given table. Any branch of the tree is terminated when it encounters a table for which NO ACTION has been specified or is the default. Triggers and Cascading Referential Actions
Cascading referential actions fire AFTER triggers in this sequence: All of the cascading referential actions directly caused by the original DELETE or UPDATE are performed first.
When the original cascading referential actions have completed, the AFTER triggers on the original table are fired, regardless of whether any rows were updated.
AFTER triggers on tables in the chain of cascaded referential actions are then fired, but only if one or more rows in the table have been updated or deleted.
If any errors are generated by any of the original set of cascading referential actions, an error is raised, no AFTER triggers are fired, and the DELETE or UPDATE is rolled back. An AFTER trigger can execute a DELETE or UPDATE statement that starts another chain of cascading referential actions. Each secondary chain of referential actions is treated independently. These secondary chains of referential actions behave like the primary chain. All of the secondary referential actions are completed before any secondary triggers are fired. Within each independent unit, there is no defined order in which the cascading referential actions are executed and the affected triggers are fired. A table that has an INSTEAD OF trigger cannot also have a REFERENCES clause that specifies a cascading action. An AFTER trigger on a table targeted by a cascading action, however, can execute an INSERT, UPDATE, or DELETE statement on another table or view that fires an INSTEAD OF trigger defined on that object. Cascading Referential Constraints Catalog Information
The following catalog information is available about cascading referential constraints. The Transact-SQL OBJECTPROPERTY function supports these new values for the property parameter. Value Object Description
CnstIsDeleteCascade Constraint FOREIGN KEY constraint defined with ON DELETE CASCADE
CnstIsUpdateCascade Constraint FOREIGN KEY constraint defined with ON UPDATE CASCADE
The REFERENTIAL_CONSTRAINTS information schema view returns CASCADE in the UDPATE_RULE or DELETE_RULE column when either ON UPDATE CASCADE or ON DELETE CASCADE is specified. NO ACTION is returned when either ON UPDATE NO ACTION or ON DELETE NO ACTION is specified, or if ON UPDATE or ON DELETE is not specified at all. The UPDATE_RULE and DELETE_RULE columns returned by sp_fkeys and sp_foreignkeys are set to 1 when CASCADE is specified, and return 0 when NO ACTION is specified or is the default. When a foreign key is specified as the object of sp_help, the output result set contains these new columns. Column name Data type Description
delete_action nvarchar(9) Indicates whether the delete action is CASCADE, NO ACTION, or N/A (not applicable).
update_action nvarchar(9) Indicates whether the update action is CASCADE, NO ACTION, or N/A (not applicable).
Hope help.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

Yes, use ALTER TABLE tablename NOCHECK CONSTRAINT ,….. and refer to books online for complete information. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

]]>