Thread: 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, givencreate 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
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
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.