A table that has relation to itself issue

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

How about an OR clause?

Delete From TableName
Where ID = 4 OR ParentId = 4

Answer 5

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.