> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
