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

From Tom Lane
Subject Re: Foreign key column reference ordering and information_schema
Date
Msg-id 20277.1147879188@sss.pgh.pa.us
Whole thread Raw
In response to Foreign key column reference ordering and information_schema  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
Responses Re: Foreign key column reference ordering and information_schema
Re: Foreign key column reference ordering and information_schema
List pgsql-hackers
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.

What I see in SQL99 is (11.8 <referential constraint definition>)
   If the <referenced table and columns> specifies a <reference   column list>, then the set of <column name>s
contained  in that <reference column list> shall be equal to the   set of <column name>s contained in the <unique
column  list> of a unique constraint of the referenced table. Let   referenced columns be the column or columns
identifiedby   that <reference column list> and let referenced column be one   such column. Each referenced column
shallidentify a column   of the referenced table and the same column shall not be   identified more than once.
 

where SQL2003 has
   If the <referenced table and columns> specifies a <reference column   list>, then there shall be a one-to-one
correspondencebetween the   set of <column name>s contained in that <reference column list>   and the set of <column
name>scontained in the <unique column   list> of a unique constraint of the referenced table such that   corresponding
<columnname>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
tableand 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.

> The information_schema definition seems to require this in order for
> one to use the information to find out the column references.

I'm more inclined to think that we've messed up the information_schema
somehow ...
        regards, tom lane


pgsql-hackers by date:

Previous
From: "Jim C. Nasby"
Date:
Subject: Re: PL/pgSQL 'i = i + 1' Syntax
Next
From: "Zeugswetter Andreas DCP SD"
Date:
Subject: Re: pg_dump and backslash escapes