Re: Foreign key column reference ordering and information_schema - Mailing list pgsql-hackers

From Mark Dilger
Subject Re: Foreign key column reference ordering and information_schema
Date
Msg-id 446BA78E.9010109@markdilger.com
Whole thread Raw
In response to Re: Foreign key column reference ordering and information_schema  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
List pgsql-hackers
Stephan Szabo wrote:
> On Wed, 17 May 2006, Tom Lane wrote:
> 
> 
>>Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
>>
>>>Per the report from Clark C Evans a while back and associated discussion,
>>>it seems like recent versions of the SQL spec changed the rules for
>>>foreign key column references such that the columns of the referenced
>>>unique constraint must be named in order (this is somewhat silly since
>>>unique(a,b) really should imply unique(b,a) but...).
>>
>>I do not believe that that reading is correct.  If the SQL committee had
>>intended such a change, it would surely have been called out as a
>>compatibility issue in Annex E of SQL2003.  Which it isn't.
>>
>>where SQL2003 has
>>
>>    If the <referenced table and columns> specifies a <reference column
>>    list>, then there shall be a one-to-one correspondence between the
>>    set of <column name>s contained in that <reference column list>
>>    and the set of <column name>s contained in the <unique column
>>    list> of a unique constraint of the referenced table such that
>>    corresponding <column name>s are equivalent. Let referenced columns
>>    be the column or columns identified by that <reference column
>>    list> and let referenced column be one such column. Each referenced
>>    column shall identify a column of the referenced table and the same
>>    column shall not be identified more than once.
>>
>>I think SQL2003 is actually just trying to say the same thing in more
>>precise language: you have to be able to match up the columns in the
>><reference list> with some unique constraint.  I don't think the "one
>>to one" bit is meant to imply a left-to-right-ordered correspondence;
>>that's certainly not the mathematical meaning of a one-to-one function
>>for instance.
> 
> 
> No, but the part which says corresponding column names are equivalent
> seems to imply it to me.
> 

The language quoted above uses the language "set of X contained in list Y"
multiple times (substituting X and Y).  The only reason to do so would be to
invoke the mathematical distinction between lists and sets, which is that sets
do not imply any specific ordering.


pgsql-hackers by date:

Previous
From: Robert Watson
Date:
Subject: Re: semaphore usage "port based"?
Next
From: "Jonah H. Harris"
Date:
Subject: Re: PL/pgSQL 'i = i + 1' Syntax