on delete cascade & hierarchical table

[anselme]

for MS SQL 2000
I am trying to do a hierarchical table and i want to add a ON DELETE CASCADE


CREATE TABLE [dbo].[Users](
[id_Users] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[id_UsersSup] [int] NULL,
[Users] [nvarchar] (100) NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[Users] ADD
CONSTRAINT [FK_Users_Sup] FOREIGN KEY
(
[id_UsersSup]
) REFERENCES [Users] (
[id_Users]
)
ON DELETE CASCADE


but MS SQL refuse to create the foreign key
even if there is 4 levels under the deleted id_Users I want to delete all the rows on all levels under

thank you for helping

[skhanal]

You are creating self referencing FK with CASCADE option, I don't think that is allowed.

[anselme]

it seems that it is not

[rmiao]

Don't think sql allows it on single table.