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

From: Alban Hertroys
Subject: Re: ? about Composite Keys + ON DELETE/UPDATE SET NULL
Date: ,
Msg-id: 83BACC99-BB2C-46EF-A700-55ECE7DEA272@solfertje.student.utwente.nl
(view: Whole thread, Raw)
In response to: ? about Composite Keys + ON DELETE/UPDATE SET NULL  ("David Johnston")
Responses: Re: ? about Composite Keys + ON DELETE/UPDATE SET NULL  ("David Johnston")
List: pgsql-general

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!




pgsql-general by date:

From: Adrian Klaver
Date:
Subject: Re: No suitable driver found for jdbc:postgresql [error]
From: Prabhat Kumar
Date:
Subject: Re: Schema for Website Comments