Re: [PATCH] Support for foreign keys with arrays - Mailing list pgsql-hackers

Hi guys,<br /><br />    it is time to give another go to this patch. I would like to thank everyone for suggestions
andideas expressed through this list.<br /><br />    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
notrush though and focus on "Part 1" of the patch. :)<br /><blockquote
cite="mid:CA+U5nMKvmv_c35D5W=O4boW1AAZFg+2qHKLbvXV3nJVdYgff8w@mail.gmail.com"type="cite"></blockquote>    First, I
wouldlike 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
wecan extend the usage). Our proposals are:<br /><br /> * Array Foreign Key<br /> * Foreign Key Arrays<br /> * ELEMENT
ForeignKeys<br /> * ...<br /><br />    Which one is your favourite?<br /><br />    Secondly, we have decided to split
thepatch we proposed back in March in two smaller patches. The most important goal of "Part 1" is to find a generally
acceptedsyntax. By removing ACTION handling from "Part 1" (see limitations below), we believe that the community will
beable to contribute more to driving future directions and requirements. Based on Peter's comments, we would like to
proposethe use of the "ELEMENT" keyword, rather than the "EACH" keyword proposed in March. You can find three examples
atthe bottom of this email.<br /><br />    Finally, "Part 1" of this patch will have these limitations:<br /><br /> *
Onlyone <code>ELEMENT</code> column allowed in a multi-column key (same as the proposed patch in March)<br /> *
Supportedactions<code>:<br />  * NO ACTION</code><code><br />  * RESTRICT</code><br /><br /> Cheers,<br /> Gabriele<br
/><br/><br /> Example 1: inline usage<br /><br /><tt>CREATE TABLE drivers (<br />     driver_id integer PRIMARY KEY,<br
/>    first_name text,<br />     last_name text,<br />     ...<br /> );<br /><br /> CREATE TABLE races (<br />    
race_idinteger PRIMARY KEY,<br />     title text,<br />     race_day DATE,<br />     ...<br />     final_positions
integer[]ELEMENT REFERENCES drivers<br /> );</tt><br /><br /><br /><br /> Example 2: with FOREIGN KEY<br /><br
/><tt>CREATETABLE races (<br />     race_id integer PRIMARY KEY,<br />     title text,<br />     race_day DATE,<br />
   ...<br />     final_positions integer[],<br />     FOREIGN KEY (ELEMENT final_positions) REFERENCES drivers<br />
);<br/></tt><br /><br /><br /> Example 3: with ALTER TABLE<br /><br /><tt>CREATE TABLE races (<br />     race_id
integerPRIMARY KEY,<br />     title text,<br />     race_day DATE,<br />     ...<br />     final_positions integer[]<br
/>);<br /><br /> ALTER TABLE races ADD FOREIGN KEY (ELEMENT final_positions) REFERENCES drivers;<br /></tt><br /><pre
class="moz-signature"cols="72">-- Gabriele Bartolini - 2ndQuadrant ItaliaPostgreSQL Training, Services and Support<a
class="moz-txt-link-abbreviated"href="mailto:gabriele.bartolini@2ndQuadrant.it">gabriele.bartolini@2ndQuadrant.it</a> |
<aclass="moz-txt-link-abbreviated" href="http://www.2ndQuadrant.it">www.2ndQuadrant.it</a>
 
</pre>