Re: Foreign key column reference ordering and information_schema

<ergh, hit send before finishing>

On Wed, 17 May 2006, Stephan Szabo wrote:

> 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.

Or are you thinking that the corresponding column names are equivalent is
just a description of how to make the correspondence?

That seems like a very odd way to phrase that since just saying that the
sets of column names are equivalent would be enough for that and all the
extra words seem to only obscure the point.