Setting to null would break the list if the delete was anywhere but the first or last point. MySQL ON DELETE CASCADE is a MySQL referential action for a MySQL foreign key that permits to remove of records automatically from the child-related tables. (Deletes would be a different issue but that wasn't addressed with the previous design either. The UPDATE will work fine with this setup. I have two foreign keys in transactions table and thats way its not. ![]() The following query can then be used to show what you have now: SELECT c.calendar_id, However I had tried to delete payment record in phpMyAdmin and cascading delete. Alternatively, you can also trigger sql query for the same. A possible workaround is to create another table for the linked list: CREATE TABLE calendars DELETE CASCADE specified rows will be deleted when the referenced rows are deleted. You have identified the cause of the problem. I really can't figure out what I am doing wrong. 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): If the cascade keyword is added to a query written for the. In other words, if I try this: UPDATE `calendars` SET `calendar_id` = 7 WHERE `calendar_id` = 5 The keyword CASCADE is used as a conjunction while writing the query of ON DELETE or ON UPDATE. 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. The problem comes when I try to modify the ID of an element. This works fine on my server and on sqlfiddle. Maybe the key didn't register or another foreign key is blocking the delete. ALTER TABLE `calendars`ĪDD CONSTRAINT `calendars_ibfk_3` FOREIGN KEY (`calendar_next`) REFERENCES `calendars` (`calendar_id`) ON DELETE SET NULL ON UPDATE CASCADE,ĪDD CONSTRAINT `calendars_ibfk_2` FOREIGN KEY (`calendar_prev`) REFERENCES `calendars` (`calendar_id`) ON DELETE SET NULL ON UPDATE CASCADE 1 Answer Sorted by: 0 I don't see any problem with the SQL in itself, i don't think the problem reside in this particular foreign key. | calendar_id | calendar_prev | calendar_next | If a row is the first in the chronological order, the value of the previous element would be null. So I made two foreign keys that reference to the primary key of the same table. All children of the parent row are killed (deleted), too. In order to have a chronological order, I made two columns that reference to the previous and to the next event. 6 Answers Sorted by: 219 If you like the Parent and Child terms and you feel they are easy to be remembered, you may like the translation of ON DELETE CASCADE to Leave No Orphans Which means that when a Parent row is deleted (killed), no orphan row should stay alive in the Child table. In Mysql 5.5, I have a table, in which I store some informations about "events".
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |