Re: Foreign key column reference ordering and information_schema

On Wed, 17 May 2006, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)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.