Re: constraints and sql92 information_schema compliance

["Clark C. Evans" <cce(at)clarkevans(dot)com>]

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.

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

| The sets in this case being the referenced columns and the unique
| columns in the unique constraint.

Not sure I get this; sorry about being so obscure in my first
email.  I hope this one clarifies the problem.

Cheers,

Clark