0

In my current project I am creating a very simple 1-to-1 messaging service. I have two tables, users and messages. The current structure of the messages table is like this:

id  | from | to    | message    | sent     | received
int | int  | int   | string     | datetime | datetime

The from and to fields are both foreign keys to the users primary key (id). That seems to work fine but I cannot make them both use cascading deletes. I get this error:

Introducing FOREIGN KEY constraint 'FK_messages_users_to' on table
'messages' may cause cycles or multiple cascade paths. Specify ON 
DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.

Can this be done? I don't see why it doesn't work. If a user was deleted I want to delete the entire conversation so any rows that have the users id in either the from or the to field.

James Hay
  • 7,115
  • 6
  • 33
  • 57
  • have you seen this: http://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths – mcalex Oct 13 '12 at 04:07
  • I have not, thanks. So basically the answer is no you can't do it because it's too complex for SQL software to calculate. Do you have any ideas for a design that will be better? I've started by creating the database first because I figured I'd come across something like this – James Hay Oct 13 '12 at 04:16
  • I'm afraid not - except do stuff the long way. Before deleting the user find their messages and delete them. See if you now have any orphans from the To side of the conversation and remove them (assuming you want to delete users if they have no messages?), and then delete the sender. – mcalex Oct 13 '12 at 04:48

0 Answers0