SQL issue, mutual constraints, "a foreign key constraint fails"

Question

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!

Answer 1

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.