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
nicelyunless 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
erroris 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