DB : Circular dependency in the database

Question

EDIT: This is a duplicate I didn't find it at first.

I am building a web-site for user-groups event management.

Members : Name, Id  
Events : DateTime, Topic, OrganizerId (from FK to Members table)
EventRegistrations : MemberId (FK), EventId (FK)

Description (redundant) :
A member can create and event, and becomes this event's organizer.
Any member can register for an event, and a record in EventRegistrations is created.

The Problem:
When I create PK-FK dependency between the tables, I get an error saying:

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

Question:
What is a good way to deal with this? I have two solutions to begin with:

1. Introduce 4th (associating) table "Organizers : MemberId -> EventId".
2. Disable CASCADE DELETE on one of the dependencies, and do check programmatically before DELETE operations.

I'm looking for suggestions or a feedback on the above. Extra explanations/comments are appreciated.

Note: DB is SQL Server 2008, but it shouldn't matter, I think.

EDIT: Suppose deleting an event IS required behavior (example is simplified).

Answer 1

I guess this is not a circular dependency, but rather a "multiple cascade path" problem (don't remember the exact error message).

The solution I'd prefer is to remove the OrganizerId CASCADE DELETE, since deleting an organizing member should not automatically remove the member's events, and implement this part as an SP.

You can still implement child record deletion in an INSTEAD OF DELETE trigger, or in an SP.

Answer 2

Are you sure the CASCADE DELETE is a good idea? If the organizer is being deleted, the event still ocurred (thinking about events in the past), so I think it is wrong to delete the associated event, from a data model point of view.

Some social networks delete messages a certain user sent to you in the past, which I find extremely annoying, because they are deleting my data. Had the user sent an e-mail or, for that matter, even snail mail, I'd still have the message and the users name.

Answer 3

Not sure why you're getting a circular dependency. Are you sure you have declared the relationhips with the correct cardinaility?

Your tables ...

SQL> create table members
  2      ( name varchar2(10)
  3          , id  number not null primary key   )
  4  /

Table created.

SQL> create table events
  2      ( id  number not null primary key
  3          , datetime date
  4          , topic varchar2(10)
  5          , organizerid  number not null )
  6  /

Table created.

SQL> create table eventregistrations
  2      ( memberid  number not null
  3          , eventid  number not null)
  4  /

Table created.

SQL>

A member can organise any number of events. An event must have a single organiser.

SQL> alter table events
  2      add constraint evt_mbr_fk foreign key ( organizerid )
  3      references members (id) on delete cascade
  4  /

Table altered.

SQL>

A member can register for any number of events ...

SQL> alter table eventregistrations
  2      add constraint reg_mbr_fk foreign key ( memberid )
  3      references members (id) on delete cascade
  4  /

Table altered.

SQL>

An event can have any number of registered members ...

SQL> alter table eventregistrations
  2      add constraint reg_evt_fk foreign key ( eventid )
  3      references events (id) on delete cascade
  4  /

Table altered.

SQL>

Note: I haven't bothered implementing the rule "a member can only resister once for any given event", because it is not relevant (but it would be a compound primary key on eventregistrations.

edit

I share the others' concerns about the use of ON CASCADE DELETE but I think it is not strictly relevant to the question. Forcing a change of organizerId for an event is a business rule. In some scenarios ON DELETE CASACDE is appropriate and in some it is not.

edit 2

This is an indication that the flavour of database does make a difference after all. Oracle is quite happy to cascade a delete of MEMBERS through to EVENTS and EVENTREGISTRATIONS without complaint.

Answer 4

"DB is SQL Server 2008, but it shouldn't matter, I think."

It matters everything. Your DBMS is too crippled to offer decent support for handling your problem. So is any SQL alternative, by the way.

You can additionally :

1. Switch to SIRA_PRISE and use its support for multiple assignment and declarative database constraints of arbitrary complexity.

2. Disable the cascade delete and replace it with triggered code. Remember not to forget to think about cascade update too.