Re: constraints and sql92 information_schema compliance

On Sat, 25 Feb 2006, Clark C. Evans wrote:

> On Sat, Feb 25, 2006 at 12:51:51PM -0800, Stephan Szabo wrote:
> | > > * for foreign-key and check constraints, the default names
> | > > are $1, $2, etc.; it would be great if they were "upgraded"
> | > > to use the default names given by primary and unique key
> | > > constraints: table_uk_1stcol, table_pk
> | >
> | > Err... what version are you using? I get constraint names like tt_a_fkey
> | > from devel, and I thought at least 8.1 does the same.
>
> 7.4.8, so it's a bit old -- glad to hear this made it!
>
> | > > * when creating a foreign key constraint on two columns, say
> | > > from A (x, y) to B (x, y), if the unique index on B is (x,y)
> | > > you can make a foreign key from A->B using (y,x)
> | >
> | > I don't understand which particular case you're complaining about, but as
> | > far as I can see, we have to allow that case by the rest of the spec.
>
> To be clear, I'm talking about...
>
> 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);
>
> For this case, the information schema details:
>
> 1. the foreign key constraint as a reference to the
> primary key constraint and lists the tuple (b,c)
>
> 2. the primary key constraint lists the keys (y,z)
>
> In particular, the column ordering (z, y) in the reference
> clause is *lost*. Hence, if you were to blindly reconstruct
> a join critiera from the information schema, you'd wrongly
> assume that useful join critiera is:
>
> ON (a.b == x.y AND a.c == x.z)
>
> when the correct join critiera should be:
>
> ON (a.b == x.z AND a.c == x.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.

> | > If A(x) is comparable to B(x) and B(y) and A(y) is comparable to B(x)
> | > and B(y), all of A(x,y)->B(x,y), A(y,x)->B(x,y), A(x,y)->B(y,x) and
> | > A(y,x)->B(y,x) seem to be allowed by the definition in the constraint
> | > section (as only the sets must be equal, with no mention of ordering).
>
> Ordering of tuples (fields within a row object) are significant
> in SQL; and hence the two above are not comparable.

You misunderstand what comparable means in the above. Comparable is the
constraint on the column types (for example numeric types are comparable
to other numeric types).