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.bartolini@2ndQuadrant.it | www.2ndQuadrant.it