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