Cascade deletion of an object that can be related to itself

In my database, I have a number of objects that can be related to each other.

This is fine, until I decide I want to delete these objects. Because of the relation record, I need to implement cascade delete to prevent an exception from being thrown.

When an object that is on either side of the relation is deleted, I want the relation record to be deleted too. I would like to create a database structure that looks like this:

CREATE TABLE [MyObject]
(
     [ID] [int] IDENTITY PRIMARY KEY,
     ...
);

CREATE TABLE [MyObjectRelation]
(
     [ID] [int] IDENTITY PRIMARY KEY,
     [MyObjectID] [int] F开发者_运维问答OREIGN KEY REFERENCES [MyObject] ([ID]) ON DELETE CASCADE,
     [RelatedMyObjectID] [int] FOREIGN KEY REFERENCES [MyObject] ([ID]) ON DELETE CASCADE
)

However, whenever I attempt to run this on my database, I receive this error message:

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

I have read the documentation about this, but I do not see how this layout could cause a cycle. It is entirely possible of course, that I have misinterpreted the documentation on MSDN for cascade delete, and the database layout above will not achieve what I want here.

I would be very interested in hearing what I can do to implement the behaviour that I want.

You receive this error message because in SQL Server, a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement. For example, the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree.

You can use triggers to achieve the same behavior.