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?