From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Foreign key column reference ordering and information_schema |
Date: | 2006-05-17 15:19:48 |
Message-ID: | 20277.1147879188@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
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 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.
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.
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Zeugswetter Andreas DCP SD | 2006-05-17 15:27:33 | Re: pg_dump and backslash escapes |
Previous Message | Jim C. Nasby | 2006-05-17 15:14:08 | Re: PL/pgSQL 'i = i + 1' Syntax |