Re: constraints and sql92 information_schema compliance

On Sat, 25 Feb 2006, Stephan Szabo wrote:

>
> 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.

Okay, I'll take that back for SQL2003. They must have realized that this
was broken with information schema and changed it. That's an interesting
incompatibility with old versions, but it's easy to implement.