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.cio...@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. -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers