Hi,
Is there any way to effect behavior similar to the following:
FOREIGN KEY (field1, field2)
REFERENCES table2 (field1, field2)
ON UPDATE CASCADE
ON DELETE (SET field2 = NULL) -- leaving field1 with whatever value is currently holds
With MATCH SIMPLE the NULL in field2 is sufficient to “break” the Foreign Key and let the record on table2 become deleted.
Given the general disdain for composite keys I can see why this particular behavior has been overlooked but it does seem reasonable, in the presence of “MATCH SIMPLE”, to specify that only some of the constrained fields be affected by “ON DELETE SET NULL”.
Some syntax like:
ON DELETE SET NULL(fieldn [, fieldn+1 …]) would seem to be the most declarative way to accomplish this.
Alternatively, having the ability to fire a trigger function would make custom behavior possible since the trigger function could just do a “NEW.field2 = NULL” and then return NEW.
Like: ON DELETE CALL trigger_function();
In my particular use-case I have a field on the FK table (invoice number) and, in the presence of a (store id) it wants to enforce that the physical invoice exists for that particular store. Should the physical invoice become deleted I want to still leave the (invoice number) present but set the (store id) back to NULL. I know, and can consider, other possibilities but the first thing that came to mind was using “ON DELETE SET NULL(field2)” and so I figure I might as well toss it out here and see what others think.
Thanks,
David J.