Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: on delete cascade & hierarchical table

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    I tried running your code on SQL Server 2005, and got the following message:

    Introducing FOREIGN KEY constraint 'FK_Users_Sup' on table 'Users' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

    In my opinion, this error message tells pretty well why you are not allowed to use on delete cascade on a self-join.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You will need to implement this cascading referential integrity using a trigger.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •