Database cascading error

Question

I have three tables in my database: Events, Jobs, and CollectableEntities.

Events has a nullable FK pointing to Jobs' PK.

Events and Jobs both have non-nullable FKs (that are also those table's PKs) pointing to CollectableEntities' PK. These FK relationships are set to cascade on update and delete. Basically, when you delete a CollectableEntity, I want the associated Event and/or Job to be deleted also.

I am now trying to change the Events to Jobs FK relationship to set null on update or delete, but I get the following error:

Error SQL01268: .Net SqlClient Data Provider: Msg 1785, Level 16, State 0, Line 1 Introducing FOREIGN KEY constraint 'FK_Events_Jobs' on table 'Events' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Where is the cycle? How can I avoid this problem given that the description above is the only effects I want. (I'm willing to make changes to avoid other unforeseen side-effects.)

Answer 1

it would probably just be a lot easier to mark everything as "deleted" instead of actually deleting data out of the database. so on your tables you would have a bit field called Deleted that defaults to false. Then, whenever something is deleted that record gets marked as deleted. After that you just set up a where statement in your queries to removed the deleted items and you're done.

Answer 2

See the http://allyourdatabase.blogspot.com/2006/11/multiple-cascade-paths-error-in-sql.html

You have a very similar situation... deleting from collectables is going to attempt a delete from events, which is going to attempt a delete from jobs due to the FK from events to jobs. But deleting from collectables is also going to attempt deleting from jobs due to the FK from collectables to jobs. Having jobs in the cascade path twice is a problem.

I might be a little off on the specifics since you didn't include your schema, but the general principal should be correct.