Re: constraints and sql92 information_schema compliance

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

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