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