Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id D493816AF4E5 for ; Mon, 30 Jul 2012 12:12:55 -0300 (ADT) Received: from delta01.pnet.xcon.it ([62.48.53.16]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SvreP-0000CH-4Q for pgsql-hackers@postgresql.org; Mon, 30 Jul 2012 15:12:55 +0000 Received: from localhost (localhost [127.0.0.1]) by delta01.pnet.xcon.it (Postfix) with ESMTP id EF1F310336DA8; Mon, 30 Jul 2012 17:12:38 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at delta01.pnet.xcon.it Received: from delta01.pnet.xcon.it ([127.0.0.1]) by localhost (delta01.pnet.xcon.it [127.0.0.1]) (amavisd-new, port 10024) with LMTP id dVo2ZETzeldH; Mon, 30 Jul 2012 17:12:38 +0200 (CEST) Received: from Gabriele.local (unknown [83.149.163.202]) (Authenticated sender: gabriele.bartolini@devise.it) by delta01.pnet.xcon.it (Postfix) with ESMTPSA id 4050B10336D92; Mon, 30 Jul 2012 17:12:38 +0200 (CEST) Message-ID: <5016A466.4070603@2ndQuadrant.it> Date: Mon, 30 Jul 2012 17:12:38 +0200 From: Gabriele Bartolini User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.7; rv:14.0) Gecko/20120713 Thunderbird/14.0 MIME-Version: 1.0 To: Simon Riggs CC: Peter Eisentraut , Gianni Ciolli , Tom Lane , Marco Nenciarini , pgsql-hackers@postgresql.org, Noah Misch Subject: Re: [PATCH] Support for foreign keys with arrays References: <20111212004506.GB10399@tornado.leadboat.com> <1326568728.2613.16.camel@greygoo.devise-it.lan> <20120121204223.GA2388@tornado.leadboat.com> <4F280EFE.1080604@2ndQuadrant.it> <1328551482.3354.64.camel@greygoo.devise-it.lan> <20120225020135.GA27204@tornado.leadboat.com> <1331748188.6744.15.camel@greygoo.devise-it.lan> <20120317033312.GB19556@tornado.leadboat.com> <1332178899.2278.4.camel@greygoo.devise-it.lan> <12609.1332442965@sss.pgh.pa.us> <20120324100131.GA13561@leggeri.gi.lan> <1333693277.32606.9.camel@vanquo.pezone.net> In-Reply-To: Content-Type: multipart/alternative; boundary="------------070500040605000400050906" X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201207/1099 X-Sequence-Number: 211782 This is a multi-part message in MIME format. --------------070500040605000400050906 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 --------------070500040605000400050906 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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
--------------070500040605000400050906--