Re: [PATCH] Support for foreign keys with arrays

On Sun, Nov 20, 2011 at 10:36:15AM +0100, Gabriele Bartolini wrote:
> I would agree with what Tom is saying here, given that SQL specs do not
> say anything about this feature. We could leave standard REFERENCES
> keyword handling the array value as it is now. If a user wants to take
> advantage of in-array referential integrity, we could implement the
> special keyword "ARRAY REFERENCES" as Tom proposes (or a similar
> keyword).

No objection to that.

> --------------- --------- ---------
> | ON | ON |
> Action | DELETE | UPDATE |
> --------------- --------- ---------
> CASCADE | Row | Element |
> SET NULL | Row | Row |
> ARRAY CASCADE | Element | Element |
> ARRAY SET NULL | Element | Element |
> SET DEFAULT | Error | Error |
> NO ACTION | - | - |
> RESTRICT | - | - |
> --------------- --------- ---------

I like this.

> CASCADE and ARRAY CASCADE are
> synonyms, as they would work in individual elements (which is the action
> that makes more sense anyway).

What about making ON UPDATE CASCADE an error? That way, we can say that ARRAY
<action> always applies to array elements, and plain <action> always applies to
entire rows.

SET DEFAULT should now be fine to allow. It's ARRAY SET DEFAULT, in your new
terminology, that wouldn't make sense.

Thanks,
nm