Foreign key column reference ordering and information_schema
Now that I've got a little time again...
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...). The
information_schema definition seems to require this in order for one to
use the information to find out the column references.
I don't think we can switch to the newer definition directly since that
will break dumps, but we could provide a variable which controls whether
we allow the set allowed by SQL92 (not necessarily ordered) with the
default being true for compatibility.
But, that still doesn't give us a path to being able to change the
default, or for that matter making it safe to use information_schema
(since it would still be possible to change the value, make a constraint
and change it back). One way to do this would be to accept the SQL92 form
and treat it as if the command had given it in the ordered form, in other
words, given
create table pk(a int, b int, unique(a,b));
create table fk(c int, d int, foreign key(d,c) references (b,a));
the constraint is stored as if it were given foreign key(c,d)
references(a,b).
Does anyone have objections to either or both parts of this, and for the
first, does anyone have a good idea of a name for the variable that would
control this?
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 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
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.
<ergh, hit send before finishing>
On Wed, 17 May 2006, 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.
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.
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
On Wed, 17 May 2006, Tom Lane wrote:
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.
No, that's just saying the names have to actually match. Otherwise I
could say that I can put "A B C" into a one-to-one correspondence with
"D E F" because there are the same number of elements in each set.
The whole sentence was written by a pedant and is not an improvement in
intelligibility over the SQL92/99 wording, but I really think it is not
intended to imply anything functionally different. Again, if they did
intend to change the allowable matches, why doesn't Annex E mention it?
regards, tom lane
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
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.
As an example of clear well-written English, it certainly fails
miserably no matter which construction you put on it :-(.
regards, tom lane
On Wed, 17 May 2006, Tom Lane wrote:
I'm more inclined to think that we've messed up the information_schema
somehow ...
As usual, you're right. ;)
Actually, it wasn't precisely that we messed it up as much as the 99
defintion was wrong. It's pointed out in the 2003 schemata
incompatibilities annex as having been incomplete and changed for 2003.
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.