Re: constraints and sql92 information_schema compliance

[Resurrecting an old thread]

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.

Looking at this more, I'm not sure that making it match the unique key
exactly helps information_schema.constraint_column_usage at least.

Given the following:
create table ta(a int, b int, primary key(a,b));
create table tb(a int, b int, foreign key (a,b) references ta);
create table tc(a int, b int, foreign key (b,a) references ta);

I don't see how you can differentiate the foreign keys in the last two
without a position column, which doesn't seem to be in at least our
current view (although I haven't checked 2003 to see if they changed it).
Both of those should be valid, although the second is wierd.