Re: constraints and sql92 information_schema compliance

On Wed, 15 Mar 2006, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> > The main options seem to be:
> > When we're allowing other order access, immediately reorder the
> > constraint information to match the primary key order. This helps out
> > with IS since the loaded constraint should display properly, but
> > theoretically could change the visual representation after load for people
> > who don't care about this option.
> > Change the representation unconditionally on dump. Basically reorder the
> > constraint at dump time to always generate a dump in SQL03 order. This has
> > the same downside as the above except only after another dump/restore.
> > Change the representation on dump only if the flag is set (probably
> > exporting this as an option to pg_dump as well). This could be a little
> > more difficult to use, but pretty much causes the user to drive the
> > choice.
>
> I'm missing something. On what basis do you claim that there's a
> "SQL03 order", ie some ordering mandated by the spec? What problem is
> this really solving?

SQL2003 seems to change the relevant piece to:

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 read the section on corresponding column names are equivalent to imply
that (b,a) and (a,b) aren't equivalent for this purpose because the
corresponding column names are different.

That's a difference from SQL92's version which basically just says the
sets are the same.

Basically, it's a compliance point, and something that's necessary to make
information_schema work for foreign key constraints because you can't
seem to use information_schema to read how the columns line up without it
because they didn't put an ordering column on the side storing the
referenced keys.