Re: [PATCH] Support for foreign keys with arrays - Mailing list pgsql-hackers

On Thu, Mar 22, 2012 at 03:02:45PM -0400, Tom Lane wrote:
> It's even less clear about what the semantics are in multi-key
> cases.  Right offhand I would say that multi-key cases are
> nonsensical and should be forbidden outright, because there is no
> way to figure out which collections of elements of different arrays
> should be considered to be a referencing item.

Currently multi-column keys with more than one EACH column are
unsupported, mainly because it's unclear how they should work (and I
agree that they might not work at all).

> Could we see a specification of what the referencing semantics are
> intended to be, please?

You are right, the discussion has never been put together in a single
place, as it should have.

Please find below an updated version of the specification, which Marco
and I put together from the discussion in this list, and taking into
account the changes happened in the review phase. Some comments have
also been added to explain why some choices have been forbidden.

Best regards,
Dr. Gianni Ciolli - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gianni.ciolli@2ndquadrant.it | www.2ndquadrant.it

---8<------8<------8<------8<------8<------8<------8<------8<------8<---

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          |     -     |     -     |
------------------ --------- -------------

Example 1. Table C references table B via a (non-array) foreign key.

Example 2. The referencing table A is constructed as GROUP BY of table
C in Example 1. There is an EACH foreign key on A which references B,
representing the same relationship as the foreign key in Example 1.

Remark 3. Examples 1 and 2 are related, because they represent the
same model; in making choices about a certain action on Example 2 we
will considering its relationship with Example 1.

Example 4. Assume that the FK in Example 1 has a ON DELETE CASCADE
action.  Deleting one row on table B will delete all the referencing
rows in table A.  The state that we get after the DELETE is the same
obtained by Example 2 with the ON DELETE EACH CASCADE action after
removing the same row.

Example 4 suggests to associate the "Element" behaviour to the ON
DELETE EACH CASCADE action.

The user can choose between two different options for a CASCADE-style
action when a referenced row is deleted; both of them have use cases,
as the following Example 5 shows.

Example 5. If you remove a vertex from a polygon (represented as an
array of vertices), you can either destroy the polygon (ON DELETE
CASCADE) or transform it into a polygon with less vertices (ON DELETE
EACH CASCADE).

ON UPDATE SET NULL has its own purpose as a different behaviour than
ON UPDATE EACH SET NULL; again, both options are provided to the user,
essentially like with ON DELETE CASCADE and ON DELETE EACH CASCADE.

ON (UPDATE | DELETE) EACH SET DEFAULT is forbidden, because table A
does not carry a default value for an array element. In theory the
default value could be retrieved from the referenced table B, but that
would be unusual and in any case different from the corresponding case
of Example 1 with ON (UPDATE | DELETE) SET DEFAULT.

ON UPDATE CASCADE is forbidden because, as far as we can see, the only
meaningful action to propagate updated values is ON UPDATE EACH
CASCADE.