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

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