CASCADE OPTIONS v2k
Cascading of updated or deleted key data via constraints, which has 
been in Access and Oracle for some time, has finally arrived in SQL 
Server 2000. Before this new functionality, cascading of changes in key 
data usually had to be performed via application triggers. While 
triggers are still going to be around, there will likely be fewer of 
them.

Consider the following scenario:

CREATE TABLE Parent (Lname VARCHAR(10) NOT NULL PRIMARY KEY) 
GO 
CREATE TABLE Child (Id INT IDENTITY(1,1),  
 Lname VARCHAR(10) NOT NULL CONSTRAINT fk1  
 REFERENCES Parent(Lname) ON DELETE NO ACTION ON UPDATE CASCADE) 
GO 
INSERT INTO Parent VALUES ('Smith') 
INSERT INTO Child VALUES ('Smith') 
GO

The ON UPDATE CASCADE portion of the foreign key definition tells SQL 
Server to cascade or "domino" the updates made to the primary key in 
Parent to the Child table.

UPDATE Parent SET Lname = 'Johnson'

The row in both tables would now be updated from 'Smith' to 'Johnson.'

The ON DELETE NO ACTION clause tells SQL Server to do just that, take 
no action if a primary key is deleted from the Parent table. This is 
the default behavior of SQL Server. So if you left this clause out of 
your table/foreign key definitions or kept it in, then SQL would raise 
an error stating something like the following and would not let you 
delete the row.

DELETE FROM Parent WHERE Lname = 'Johnson' 
Server: Msg 547, Level 16, State 1, Line 1 DELETE statement conflicted 
with COLUMN REFERENCE constraint 'fk1'. The conflict occurred in 
database 'test', table 'Child', column 'Lname'. The statement has been 
terminated.

These options can be useful for managing your application's data, 
but this functionality is also a double-edged sword. If you were to use 
both ON DELETE CASCADE and ON UPDATE CASCADE, you could potentially 
invalidate all your data within your database if you started updating 
or deleting top-level keys.

------------------------------------------