Thread: ? about Composite Keys + ON DELETE/UPDATE SET NULL
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.
On 12 Jul 2011, at 1:02, David Johnston wrote: > 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 That's quite dependant on how the referenced table is implemented. If the referenced value gets deleted, who says that there's still a valid value for field1 in that table? Worse, what ifit's NOT unique? A foreign key can only reference one record in another table, after all. It may be safe in your case, but it's not a valid assumption in the general case. > Alternatively, having the ability to fire a trigger function would make custom behavior possible since the trigger functioncould just do a “NEW.field2 = NULL” and then return NEW. You already do have that ability. You can define a DELETE trigger. The referenced table would be the correct place for that. In the trigger function you could then also check whether a (field1, NULL)-reference to this table would be valid, meaningthat the tuple (field1, NULL) is unique in this table. The uniqueness of that tuple is equivalent to field1 being unique, because NULL means "unknown" and therefore doesn't addanything to make the tuple more or less unique (just mentioning this, it's an often overlooked fact). Alban Hertroys -- The size of a problem often equals the size of an ego. !DSPAM:737,4e1c0e4e12097122610358!
> 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 That's quite dependant on how the referenced table is implemented. If the referenced value gets deleted, who says that there's still a valid value for field1 in that table? Worse, what if it's NOT unique? A foreign key can only reference one record in another table, after all. >>>>>>>>>>>>>>>>>>>>>>>>>>>>>> I knew I was forgetting something. In my particular case an FK will not even work since the field1 value will not exist in the referenced table in many cases. I'll need to use triggers both ways to do what I want. I basically want a conditional FK where if both field1 and field2 have values they need to exist on the referenced table but if field2 is null then it does not matter whether field1 is on the other table. > 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. You already do have that ability. You can define a DELETE trigger. The referenced table would be the correct place for that. >>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Yes, thank you. I need to widen my thought process and consider how I can modify table2 as a result of actions on other tables. It is too easy to get tunnel vision when creating table2 and try to put all table2 behavior within its own CREATE TABLE definition. David J.