On 6 April 2012 07:21, Peter Eisentraut <peter_e@gmx.net> wrote:
> On lör, 2012-03-24 at 10:01 +0000, Gianni Ciolli wrote:
>> ON (DELETE | UPDATE) actions for EACH foreign keys
>> ==================================================
>>
>> ------------------ ----------- -----------
>> | ON | ON |
>> Action | DELETE | UPDATE |
>> ------------------ ----------- -----------
>> CASCADE | Row | Forbidden |
>> SET NULL | Row | Row |
>> SET DEFAULT | Row | Row |
>> EACH CASCADE | Element | Element |
>> EACH SET NULL | Element | Element |
>> EACH SET DEFAULT | Forbidden | Forbidden |
>> NO ACTION | - | - |
>> RESTRICT | - | - |
>> ------------------ --------- -------------
>>
> I took another fresh look at this feature after not having looked for a
> month or two. I think the functionality is probably OK, but I find the
> interfaces somewhat poorly named. Consider, "PostgreSQL adds EACH
> foreign keys" -- huh? I think they key word ELEMENT would be more
> descriptive and precise, and it also leaves the door open to other kind
> of non-atomic foreign key relationships outside of arrays. EACH has no
> relationship with arrays. It might as well refer to each row.
>
> On the matter of the above chart, there has been a long back and forth
> about whether the row or the element case should be the default. Both
> cases are probably useful, but unfortunately you have now settled on
> making maximum destruction the default. Additionally, we would now have
> the case that sometimes, depending on some configuration elsewhere, an
> ON DELETE CASCADE deletes more than what was actually involved in the
> foreign key. What I'd suggest is to make both cases explicit. That is,
> forbid ON DELETE CASCADE altogether and make people write ON DELETE
> CASCADE ROW or ON DELETE CASCADE ELEMENT. In addition to making things
> more explicit and safer, it would again leave the door open to other
> kinds of relationships later.
+1
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services