Re: ? about Composite Keys + ON DELETE/UPDATE SET NULL

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 if it'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 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.

In the trigger function you could then also check whether a (field1, NULL)-reference to this table would be valid, meaning that 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 add anything 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!