Re: [PATCH] Support for foreign keys with arrays

Hi guys,

it is time to give another go to this patch. I would like to thank
everyone for suggestions and ideas expressed through this list.

We are happy that "Part 0" of the patch has been committed
(array_remove() and array_replace() functions), which will be useful in
"Part 2" (too early now to talk about it). Let's not rush though and
focus on "Part 1" of the patch. :)
First, I would like to find a unique and general term for this
feature. We started with "Foreign keys with arrays" and ended up with
"EACH foreign keys". Following Peter's suggestion, we will use the
"ELEMENT" keyword (so that maybe in the future we can extend the usage).
Our proposals are:

* Array Foreign Key
* Foreign Key Arrays
* ELEMENT Foreign Keys
* ...

Which one is your favourite?

Secondly, we have decided to split the patch we proposed back in
March in two smaller patches. The most important goal of "Part 1" is to
find a generally accepted syntax. By removing ACTION handling from "Part
1" (see limitations below), we believe that the community will be able
to contribute more to driving future directions and requirements. Based
on Peter's comments, we would like to propose the use of the "ELEMENT"
keyword, rather than the "EACH" keyword proposed in March. You can find
three examples at the bottom of this email.

Finally, "Part 1" of this patch will have these limitations:

* Only one |ELEMENT| column allowed in a multi-column key (same as the
proposed patch in March)
* Supported actions|:
* NO ACTION||
* RESTRICT|

Cheers,
Gabriele

Example 1: inline usage

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

Example 2: with FOREIGN KEY

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

Example 3: with ALTER TABLE

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

ALTER TABLE races ADD FOREIGN KEY (ELEMENT final_positions) REFERENCES
drivers;

--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele(dot)bartolini(at)2ndQuadrant(dot)it | www.2ndQuadrant.it