A table that has relation to itself issue

Posted by Mostafa on Stack Overflow See other posts from Stack Overflow or by Mostafa
Published on 2010-06-16T13:54:27Z Indexed on 2010/06/16 14:02 UTC
Read the original article Hit count: 301

Filed under:
|

Hi ,

I've defined table with this schema :

CREATE TABLE [dbo].[Codings](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ParentId] [int] NULL,
    [CodeId] [int] NOT NULL,
    [Title] [nvarchar](50) COLLATE Arabic_CI_AI NOT NULL,
 CONSTRAINT [PK_Codings] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

And fill it up with data like this :

Id          ParentId    CodeId      Title
----------- ----------- ----------- ----------
1           NULL        0           Gender
2           1           1           Male
3           1           2           Female
4           NULL        0           Educational Level
5           4           1           BS
6           4           2           MS
7           4           3           PHD

Now , I'm looking for a solution , in order , When i delete a record that is parent ( like Id= 1 or 4 ), It delete all child automatically ( all records that their ParentId is 1 or 4 ) .

I supposed i can do it via relation between Id and Parent Id ( and set cascade for delete rule ) , But when i do that in MMS , the Delete Rule or Update Rule in Properties is disabled .

My question is , What can i do to accomplish this ?

Thank you

© Stack Overflow or respective owner

Related posts about sql-server-2005

Related posts about relational