3

I have a problem with mutual constraints. I want to have two tables each having a constraint on the other one.

I'm working with Doctrine2 (but it's not related to the problem), here is my simplified code:

SQL:

CREATE TABLE IF NOT EXISTS `thread` ( `id` int(11) NOT NULL AUTO_INCREMENT, `last_message_id` int(11) DEFAULT NULL, `subject` varchar(255) NOT NULL PRIMARY KEY (`id`), UNIQUE KEY `UNIQ_C023F2BBBA0E79C3` (`last_message_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE `thread` ADD CONSTRAINT `FK_C023F2BBBA0E79C3` FOREIGN KEY (`last_message_id`) REFERENCES `message` (`id`); CREATE TABLE IF NOT EXISTS `message` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, `thread_id` int(11) DEFAULT NULL, `body` longtext NOT NULL PRIMARY KEY (`id`), KEY `IDX_9E4E8B5FA76ED395` (`user_id`), KEY `IDX_9E4E8B5FE2904019` (`thread_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE `message` ADD CONSTRAINT `FK_9E4E8B5FE2904019` FOREIGN KEY (`thread_id`) REFERENCES `thread` (`id`) ON DELETE CASCADE;

Doctrine2 mapping (which generated the SQL code above):

<?php class Thread { /* @ORM\OneToOne() */ private $lastMessage; } class Message { /* @ORM\ManyToOne() */ private $thread; }

And when I try to delete either a thread or a message, I get (logically) the error: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails ('thread', CONSTRAINT 'FK_C023F2BBBA0E79C3' FOREIGN KEY ('last_message_id') REFERENCES 'message' ('id'))

So, is there a way to avoid this error? Or should I forget mutual constraints? Anything?

I want to add that I want to keep the last_message_id because I want to display the threads with infos on their last message, and making a (paginated) query without this reference to the last message was a total nightmare...

Thanks!

Improve this question
3
  • can you post your table schema or diagram, code in this instance isn't really helpful. Commented Mar 30, 2012 at 17:52
  • You need to modify the lastMessage value in the thread table prior to deleting the current last message. If there are no messages left, i.e., the thread is empty, then lastMessage should be set to NULL. Commented Mar 30, 2012 at 17:54
  • Your SQL code does not include the FOREIGN KEY constraints (not that they are needed, the problem is clear, just for completeness). Commented Mar 30, 2012 at 17:57

4 Answers 4

Reset to default
3

Circular paths in FOREIGN KEY constraints are hard to deal with and your problem is an example. If you can avoid them, do that. Here's one way to redesign your tables:

First, add a UNIQUE KEY in table message on (thread_id, message_id) (or make it the Primary Key, if Doctrine can do that. That would mean - for MySQL- that message(id) would not be auto-incremented but produced by the ORM. You may don't want that if you plan to have applications that access the database directly or through other ORMs).

Then move the last_message_id to a new table that has a 1-to-1 relationship with message though the compound (thread_id, message_id). In this table, the thread_id would be Unique so every thread has exactly one last message.

I'll write the SQL code here. This page will help you with the Doctrine code which may produce slightly different structure: Compound Primary and Foreign Keys

CREATE TABLE IF NOT EXISTS `thread` ( `id` int(11) NOT NULL AUTO_INCREMENT, ---`last_message_id` int(11) DEFAULT NULL, --- REMOVED: last_message `subject` varchar(255) NOT NULL PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `message` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, `thread_id` int(11) NOT NULL, --- why was it NULL ? `body` longtext NOT NULL PRIMARY KEY (`id`), KEY `IDX_9E4E8B5FA76ED395` (`user_id`), ---KEY `IDX_9E4E8B5FE2904019` (`thread_id`), --- REMOVED, not needed any more --- because we have a this key UNIQUE KEY (thread_id, id) --- ADDED, needed for the FK below ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE `message` ADD CONSTRAINT `FK_9E4E8B5FE2904019` FOREIGN KEY (`thread_id`) REFERENCES `thread` (`id`) ON DELETE CASCADE;

And the new table, to store the last message for each thread:

CREATE TABLE IF NOT EXISTS `thread_last_message` ( `message_id` int(11) NOT NULL, `thread_id` int(11) NOT NULL, PRIMARY KEY (`thread_id`), KEY (`thread_id`, message_id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE `thread_last_message` --- which just means ADD CONSTRAINT `FK_something` --- that every FOREIGN KEY (`thread_id`, `message_id`) --- thread's last message REFERENCES `message` (`thread_id`, `id`) --- is a message ON DELETE CASCADE;

Another possibility is to have the thread(last_message_id) column NULL and change the FK constraints appropriately (as @Eric's proposal). This is less fussy in the design phase and you have one table less to deal with. You have to be careful with the order of inserts and deletes in this approach - as your example shows.

As a third option, have you thought if you really need a thread(last_message_id) column in your table? Couldn't this be a computed (from the two tables) value and you skip the whole issue? If it was a best_message_id I would understand this but the last message is just the last row in another table, ordered by time. You can find that with a query and you don't need to store it (again) in the database, unless there are performance reasons.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for the long answer. For the third option, I already tried to make a query that could fetch threads and their last message, but I ran into problems because I need to paginate the query...
2

The best solution I can think of would be to add a ON DELETE CASCADE constraint to the FK on the Thread table. That way if you delete the thread, the associated messages would be automatically deleted as well.

Similarly, you would need to add a ON DELETE SET NULL constraint on the Messages table FK so that if you deleted the last message in a Thread, it would set the last_message_id to NULL on the Thread table.

Or you could just do logical (soft) deletes instead of hard deletes, which would also solve the problem.

ETA:

Now that you've posted the constraints, this is the one you would have to modify:

ALTER TABLE `thread` ADD CONSTRAINT `FK_C023F2BBBA0E79C3` FOREIGN KEY (`last_message_id`) REFERENCES `message` (`id`) ON DELETE SET NULL;
Improve this answer

Comments

0

If you have mutual constraints (ie every message has a thread and every thread has a message) why can't you combine this into one table? Seems to make more sense that way

Improve this answer

1 Comment

Not possible since one thread has several messages but only one last message
0

This solution does not require altering the schema, which by the way, you have to undo.

If you want to remove a thread, the messages on that thread do not make sense either, so:

-- break one end of the mutual constraint update thread set last_message_id = NULL where id = <thread_id_to_delete>; delete from message where thread_id = <thread_id_to_delete> delete from threads where id = <thread_id_to_delete>

(Disclaimer: I did not test this exact code, but a similar one)

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.