A table that has relation to itself issue
- by Mostafa
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
