Re: constraints and sql92 information_schema compliance

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

-> the problem with $1 is that they arn't unique across
tables, and hence won't work /w information_schema
nicely unless you manually name the constraints

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

-> this might seem correct, but it makes it impossible to
deterine from the information schema which columns to
join on -- and you might infer the wrong relation
ie, FROM A JOIN B ON (b.x = a.y and b.y = a.x)

* it would be great to add a "warning" if a constraint
is not unique within its schema (obviously, making it
an error is a bad idea)

I think with these changes no updates to the views are necessary;
and compliance with the information_schema is more or less
automatic unless someone is ignoring the warnings.

Kind Regards,

Clark