Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
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. –  Riccardo Lombardi Jan 4 at 20:10
    
Post is as an answer. –  Mihai Jan 4 at 20:19
    
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. –  a1ex07 Jan 5 at 17:47
    
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? –  ypercube Jan 5 at 23:58

1 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.)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.