MYSQL - Problem with foreign keys on same table

Question

In Mysql 5.5, I have a table, in which I store some informations about "events".. In order to have a chronological order, I made two columns that reference to the previous and to the next event. So I made two foreign keys that reference to the primary key of the same table. If a row is the first in the chronological order, the value of the previous element would be null.

So, my table is:

calendars
--------
calendar_id (PRIMARY KEY)
calendar_prev
calendar_next
other columns...

I have this data (as an example)

+--------------+---------------+---------------+
| calendar_id | calendar_prev | calendar_next |
+--------------+---------------+---------------+
| 1 | NULL | 2 |
| 2 | 1 | 3 |
| 3 | 2 | 4 |
| 4 | 3 | 5 |
| 5 | 4 | NULL |
+--------------+---------------+---------------+

and these foreign keys.

ALTER TABLE `calendars`
ADD CONSTRAINT `calendars_ibfk_3` FOREIGN KEY (`calendar_next`) REFERENCES `calendars` (`calendar_id`) ON DELETE SET NULL ON UPDATE CASCADE,
ADD CONSTRAINT `calendars_ibfk_2` FOREIGN KEY (`calendar_prev`) REFERENCES `calendars` (`calendar_id`) ON DELETE SET NULL ON UPDATE CASCADE;

This works fine on my server and on sqlfiddle.

The problem comes when I try to modify the ID of an element. If I'm not missing something about how foreign keys work, if I edit the calendar_id 5 and set it, for instance, to 7, the row with calendar_id = 4 should change the calendar_next value as well, from 5 to 7. But i just get an error. In other words, if I try this:

UPDATE `calendars` SET `calendar_id` = 7 WHERE `calendar_id` = 5;

I get this error:

 Cannot delete or update a parent row: a foreign key constraint fails (`db_2_813f44`.`calendars`, CONSTRAINT `calendars_ibfk_3` FOREIGN KEY (`calendar_next`) REFERENCES `calendars` (`calendar_id`) ON DELETE SET NULL ON UPDATE CASCADE):

I really can't figure out what I am doing wrong.

• R. Lombardi: Sorry, I just found that this is a known limitation of Mysql. dev.mysql.com/doc/refman/5.5/en/… "If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates." Will have to look for a workaround.
• Mihai: Post is as an answer.
• a1ex07: Side note. It's a best practice to never update primary key value. For a normal workflow it's always possible to redesign the logic the way update is not needed.
• ypercubeᵀᴹ: If you only want to use this for storing chronological order, why don't you add a simple datetime column and completely remove the 2 calendar prev and next columns?

Answer 1

You have identified the cause of the problem. A possible workaround is to create another table for the linked list:

CREATE TABLE calendars
 ( calendar_id INT PRIMARY KEY
 -- other columns
 ) ;
CREATE TABLE calendar_list
 ( calendar_id INT NOT NULL
 , calendar_next INT NOT NULL
 , PRIMARY KEY (calendar_id)
 , UNIQUE (calendar_next)
 , CONSTRAINT calendar_list_1
 FOREIGN KEY (calendar_id) 
 REFERENCES calendars (calendar_id) 
 ON DELETE CASCADE ON UPDATE CASCADE
 , CONSTRAINT calendar_list_2
 FOREIGN KEY (calendar_next) 
 REFERENCES calendars (calendar_id) 
 ON DELETE CASCADE ON UPDATE CASCADE
 ) ;

The following query can then be used to show what you have now:

SELECT c.calendar_id, 
 prev.calendar_id AS calendar_prev, 
 next.calendar_next
FROM calendars AS c
 LEFT JOIN calendar_list AS prev
 ON c.calendar_id = prev.calendar_next
 LEFT JOIN calendar_list AS next
 ON c.calendar_id = next.calendar_id ;

The UPDATE will work fine with this setup. Test at SQL-Fiddle . (Deletes would be a different issue but that wasn't addressed with the previous design either. Setting to null would break the list if the delete was anywhere but the first or last point.)