[PATCH] Support for Array ELEMENT Foreign Keys

Hi,

please find attached version 1 of the patch introducing "Array
ELEMENT Foreign Keys" support. This new thread and related patch
substitute any previous discussion about "Support for foreign keys with
arrays", as anticipated in
http://archives.postgresql.org/pgsql-hackers/2012-07/msg01098.php

This patch adds:

* support for ELEMENT REFERENCES column constraint on array types
- e.g. c1 INT[] ELEMENT REFERENCES t1
* support for array ELEMENT foreign key table constraints
- e.g. FOREIGN KEY (ELEMENT c1) REFERENCES t1
* support for array ELEMENT foreign keys in multi-column foreign key
table constraints
- e.g. FOREIGN KEY (c1, ELEMENT c2) REFERENCES t1 (u1, u2)

Array ELEMENT foreign keys are a special kind of foreign key
constraint requiring the referencing column to be an array of elements
of the same type as (or a compatible type to) the referenced column in
the referenced table.
Array ELEMENT foreign keys are an extension of PostgreSQL and are not
included in the SQL standard.

An usage example for this feature is the following:

CREATE TABLE drivers (
driver_id integer PRIMARY KEY,
first_name text,
last_name text,
...
);

CREATE TABLE races (
race_id integer PRIMARY KEY,
title text,
race_day DATE,
...
final_positions integer[] ELEMENT REFERENCES drivers
);

This initial patch present the following limitations:

* Only one "ELEMENT" column allowed in a multi-column key
- e.g. FOREIGN KEY (c1, ELEMENT c2, ELEMENT c3) REFERENCES t1 (u1, u2,
u3) will throw an error
* Supported actions:
- NO ACTION
- RESTRICT

As noted in the last 9.2 commitfest, we feel it is important to
consolidate the "array ELEMENT foreign key" syntax and to postpone
decisions about referential integrity actions, allowing the community to
have a clearer understanding of the feature goals and requirements.

However, having array_replace() and array_remove() functions already
being committed and using our previous patch as a basis, we are
confident that a generally accepted syntax will come out in the next
months through community collaborative dynamics.

The patch includes documentation and an extensive coverage of tests
(element_foreign_key.sql regression test file). Co-authors of this patch
are Gabriele and Gianni from our Italian team at 2ndQuadrant.

Thank you.

Cheers,
Marco

--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco(dot)nenciarini(at)2ndQuadrant(dot)it | www.2ndQuadrant.it