Skip site navigation (1) Skip section navigation (2)

Re: constraints and sql92 information_schema compliance

From: "Clark C(dot) Evans" <cce(at)clarkevans(dot)com>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: Bruno Wolff III <bruno(at)wolff(dot)to>, "Clark C(dot) Evans" <cce(at)clarkevans(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: constraints and sql92 information_schema compliance
Date: 2006-02-25 20:01:11
Message-ID: 20060225200111.GB44528@prometheusresearch.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: James William PyeDate: 2006-02-25 20:21:34
Subject: Re: Pl/Python -- current maintainer?
Previous:From: Josh BerkusDate: 2006-02-25 19:51:55
Subject: Re: constraints and sql92 information_schema compliance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group