A table that has relation to itself issue

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   开发者_如何学Go       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 deletes all children automatically (all records that have a ParentId = 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

Edit:

When I wrote (like Id = 1 or 4) I meant the records that are parent, not a child, and I don't mean query like like Id = 1 or 4

Some friend implied I can do it via a delete trigger, but I supposed I can do it via relation

---

Maybe you have to define a index on the ParentID column first. You can't put constraints on columns that aren't indexed.

---

I always script for solutions to this, IE in the application search for the ID, then delete where all parent IDs = ID, then delete the parent record.

---

You can accomplish it with a "DELETE TRIGGER" - just use it to delete any rows that have matching parents. It's essentially the same thing a cascade-delete would do.

CREATE TRIGGER t_Codings_delete
            ON Codings
         AFTER DELETE
AS
BEGIN

    DELETE Codings
      FROM Codings c
      JOIN DELETED d -- Virtual table containing rows you just deleted
        ON c.ParentId = d.Id

END

---

How about an OR clause?

Delete From TableName
Where ID = 4 OR ParentId = 4

---

I don't think you can have cascading deletes on a self-refrencing table. You can probably do it with a join.

Delete from codings
inner join codings2 on codings.id = codings2.parentid
where codings.id = myid

Note not tested.