MySQL Foreign Keys – Solving Problems with Foreign Keys on the Same Table

constraintforeign keymysql-5.5

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.

Best Answer

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

Related Question