Cascade tree deleting in MS SQL Server Express 2005

Question

One table has name "Stages", every Stage can have 0..infinity childrens, in table Stages there are column named Parent. This column is foreigh key for same table Stages. How to make cascade deleting in this tree? I want to on deleting any row in this table, auto delete all their childrens and childrens of their childrens....etc

При таком запросе

..............................
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Stage_Stage]') AND parent_object_id = OBJECT_ID(N'[dbo].[Stage]'))
ALTER TABLE [dbo].[Stage]  WITH CHECK ADD  CONSTRAINT [FK_Stage_Stage] FOREIGN KEY([parent])
REFERENCES [dbo].[Stage] ([id]) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Stage] CHECK CONSTRAINT [FK_Stage_Stage]
GO
...............................

ошибка

Msg 1785, Level 16, State 0, Line 2
Introducing FOREIGN KEY constraint 'FK_Stage_Stage' on table 'Stage' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_Stage_Stage' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.

Answer 1

Add foreigns key with ON DELETE CASCADE option for all "child" tables.

ALTER TABLE SomeChildTable 
CONSTRAINT YOurConstraintName 
FOREIGN KEY (YourParentId)
REFERENCES YourParentTable(ParentTableId) ON DELETE CASCADE;

for new tables:

CREATE TABLE ttt
(
  ...
  CONSTRAINT YOurConstraintName 
  FOREIGN KEY (YourParentId)
  REFERENCES YourParentTable(ParentTableId) ON DELETE CASCADE
)