Re: [PATCH] Support for foreign keys with arrays

2011/11/17 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Noah Misch <noah(at)leadboat(dot)com> writes:
>> On Fri, Nov 04, 2011 at 01:48:02PM +0100, Gabriele Bartolini wrote:
>>> CREATE TABLE pt (
>>> id INTEGER PRIMARY KEY,
>>>
>>> CREATE TABLE ft (
>>> id SERIAL PRIMARY KEY,
>>> pids INTEGER[] REFERENCES pt,
>
>> I'm assuming the SQL spec says nothing about a feature like this?
>
> I'm pretty certain that the SQL spec flat out forbids this.
>
> The least we could do is invent some non-spec syntax that makes the
> intention clear, rather than having the system assume that an error case
> was intended to mean something else.  Maybe
>
>        pids INTEGER[] ARRAY REFERENCES pt,
>
> or something like that.  (ARRAY is a fully reserved word already,
> so I think this syntax should work, but I've not tried it.)

+1

Regards

Pavel Stehule

>
> BTW, has anyone thought through whether this is a sane idea at all?
> It seems to me to be full of cases that will require rather arbitrary
> decisions, like whether ON DELETE CASCADE should involve deleting the
> whole row or just one array element.
>
>                        regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>