ON DELETE CASCADE , UPDATE CASCADE / Cascading referential integrity

It allow you to define the actions sql server takes when a user attempts to delete or update a key to which existing foreign keys point.We can define this in the REFERENCES clauses of the CREATE TABLE and ALTER TABLE statements support ON DELETE and ON UPDATE clauses:

Syntax is [ ON DELETE { CASCADE | NO ACTION(Restrict) } ] , [ ON UPDATE { CASCADE | NO ACTION(Restrict) } ]

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.

Please NOTE : 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.

Details : http://msdn.microsoft.com/en-us/library/aa902684(SQL.80).aspx

Pawan Kumar

Pawankkmr@hotmail.com
Winners do things that losers don’t do.Focus.