Re: constraints and sql92 information_schema compliance

On Sun, 26 Feb 2006, Clark C. Evans wrote:

> On Sat, Feb 25, 2006 at 04:50:41PM -0500, Rod Taylor wrote:
> On Sat, Feb 25, 2006 at 02:01:00PM -0800, Stephan Szabo wrote:
> | > On Sat, 25 Feb 2006, Clark C. Evans wrote:
> | > >
> | > > CREATE TABLE x (y text, z text, PRIMARY KEY(y,z));
> | > > CREATE TABLE a (b text, c text);
> | > > ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y);
> | > >
> | > > I assert the problem here is that the FOREIGN KEY constraint
> | > > construction should have *failed* since the *tuple* (z,y)
> | > > does not infact match any unique key in table x.
> | >
> | > I disagree because the spec doesn't say that the columns must be equal
> | > or the list of columns must be the equal but that the set of columns must
> | > be equal. And in the definitions section, set is defined as an unordered
> | > collection of distinct objects.
>
> Let's use the example Rod gave us above. If the comparison for foreign
> key constraints should be done as an unorderd set, then why does the
> following fail?
>
> | k=# insert into x values ('foo', 'bar');
> | INSERT 0 1
> | k=# insert into a values ('foo', 'bar');
> | ERROR: insert or update on table "a" violates foreign key constraint
> | "a_b_fkey"
> | DETAIL: Key (b,c)=(foo,bar) is not present in table "x".
>
> While the SQL1992 specification may be horribly incorrect; the current
> behavior is not compliant with it... so this isn't a great defense. If
> PostgreSQL didn't store the order of the columns referenced, it couldn't
> provide the error above (which makes sense, given the extension).

No, because you're misunderstanding what the SQL92 spec says. The spec
says that the comparison between the (z,y) in the references and the key
definition (y,z) is unordered, not that the comparisons between (b,c) and
(z,y) are unordered.