datajoint-matlab
datajoint-matlab copied to clipboard
Cascading delete fails with non-congruent non-pk foreign refs
Follow set of tables lead to incorrect cascading delete:
Image
%{
test.Image (manual) # collection of all image data
image_id :int #unique image id
-----
image_data=null :longblob #image data
%}
classdef Image < dj.Relvar
end
MonkeyImage
%{
test.MonkeyImage (manual) # collection of monkey images
subj_id :int #unique id of the monkey
-----
-> test.Image
%}
classdef MonkeyImage < dj.Relvar
end
ModifiedMonkeyImage
%{
test.ModifiedMonkeyImage (manual) # collection of modified monkey images
-> test.MonkeyImage
modification_type :varchar(255) #type of modification
-----
-> test.Image
%}
classdef ModifiedMonkeyImage < dj.Relvar
end
In the above three tables, Image
is where all the images are actually stored as longblob
, with each image given a unique image_id
. MonkeyImage
is a convenient collection of all images for monkeys, with unique entry for each monkey identified by subj_id
. Finally, ModifiedMonkeyImage
is a collection of modified images for the monkey, uniquely identified by the subj_id
of the monkey as the modification_type
descriptor. Note that although ModifiedMonkeyImage
is a child of MonkeyImage
and that both have non-pk foreign ref back to Image
, they will point to different image entries!
With the three tables defined as above, the follow code illustrates the issue:
% insert two images, one for monkey unmodified, and one for cropped monkey image
insert(test.Image, struct('image_id', 1);
insert(test.Image, struct('image_id', 2);
% insert the image under MonkeyImage
insert(test.MonkeyImage, struct('subj_id', 100, 'image_id', 1);
% insert the modified image for "subj_id = 100", where "crop" has been applied
% note that here the image_id=2, not 1!
insert(test.ModifiedMonkeyImage, struct('subj_id', 100, 'modification_type', 'crop', 'image_id', 2);
% now try to delete "image_id=2" from "Image", this should cause cascading delete in
% test.ModifiedMonkeyImage
del(test.Image & 'image_id = 2')
% OUTPUT BELOW
ABOUT TO DELETE:
1 tuples from `edgar_sandbox`.`image` (manual)
Proceed to delete? (yes/no) > yes
Deleting from test.Image
** delete rolled back due to to error
Error using mym
Cannot delete or update a parent row: a foreign key constraint fails (`edgar_sandbox`.`modified_monkey_image`, CONSTRAINT
`modified_monkey_image_ibfk_2` FOREIGN KEY (`image_id`) REFERENCES `image` (`image_id`) ON UPDATE CASCADE)
This issue has been mentioned on datajoint/datajoint-python#15 and I am not sure if it has been appropriately dealt with in datajoint-python either. This is caused by an inappropriate cascading when there exists multiple paths from a parent table (e.g. Image
) to the target (ModifiedMonkeyImage
, here paths are Image -> ModifiedMonkeyImage
and Image -> MonkeyImage -> ModifiedMonkeyImage
).