Re: constraints and sql92 information_schema compliance

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

> On Sat, Feb 25, 2006 at 11:51:55AM -0800, Josh Berkus wrote:
> | > This has been discussed previously in a couple of threads. I believe the
> | > desire is to make it work as specified in SQL-2003, but I do not remember
> | > whether or not anyone volunteered to do the work to make it happen.
> |
> | I believe that the newsysviews follow the SQL03 permissions structure.
>
> Fantastic! The SQL92 permission structure was braindead.
>
> After some time working with the information schema, I have
> three suggestions:
>
> * 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.

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