This patch adds basic support of arrays in foreign keys, by allowing to
define a referencing column as an array of elements having the same type
as the referenced column in the referenced table.
Every NOT NULL element in the referencing array is matched against the
referenced table.
Example:
CREATE TABLE pt (
id INTEGER PRIMARY KEY,
...
);
CREATE TABLE ft (
id SERIAL PRIMARY KEY,
pids INTEGER[] REFERENCES pt,
...
);
This patch is for discussion and has been built against HEAD.
It compiles and passes all regressions tests (including specific ones -
see the src/test/regress/sql/foreign_key.sql file).
Empty arrays, multi-dimensional arrays, duplicate elements and NULL
values are allowed.
We had to enforce some limitations, due to the lack (yet) of a clear and
universally accepted behaviour and strategy.
For example, consider the ON DELETE action on the above tables: in case
of delete of a record in the 'pt' table, should we remove the whole row
or just the values from the array?
We hope we can start a discussion from here.
Current limitations:
* Only arrays of the same type as the primary key in the referenced
table are supported
* multi-column foreign keys are not supported (only single column)
* Only RESTRICT and NO ACTION methods for referential integrity
enforcement are currently supported
TODO:
* Improve check for empty arrays, which might interfere with SSI (see below)
* Verify interaction with serializable transactions
AUTHORS:
* Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
* Marco Nenciarini <marco.nenciarini@2ndQuadrant.it>
Cheers,
Gabriele (and Marco)
--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it
