Re: [PATCH] Support for foreign keys with arrays

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.