Thread

1. dropping anonymous constraints

   Ben Liblit <liblit@eecs.berkeley.edu> — 2002-07-17T04:06:30Z

   Given a table "data" with column "value", one can add a constraint that 
   has no name:
   
        ALTER TABLE data ADD CHECK (value > 0);
   
   How, then, does one remove this constraint?  The "ALTER TABLE ... DROP 
   CONSTRAINT ..." command expects to see a constraint name, but this 
   constraint is anonymous.
   
   
   

2. Re: dropping anonymous constraints

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-07-17T05:12:26Z

   Ben Liblit <liblit@eecs.berkeley.edu> writes:
   > Given a table "data" with column "value", one can add a constraint that 
   > has no name:
   >      ALTER TABLE data ADD CHECK (value > 0);
   > How, then, does one remove this constraint?
   
   The constraint *does* have a name, it's just an auto-assigned one
   (probably of the form "$n").  Try psql's \d command to check out
   constraint names.
   
   			regards, tom lane
   
   

3. Re: dropping anonymous constraints

   Ben Liblit <liblit@eecs.berkeley.edu> — 2002-07-17T08:16:58Z

   Tom Lane wrote:
   > Try psql's \d command to check out constraint names.
   
   That did it.  Thank you for the speedy reply.
   
   (I can't help but shake my head at the design of ALTER TABLE's constraint
   manipulation facilities, whose non-orthogonality requires one to step
   outside the language and use things like "\d" to accomplish this sort of
   task.  Perhaps when I have more database experience under my belt that
   will feel like less of a kludge.)
   
   In any case, thanks again for the help!
   
   
   
   

4. Re: dropping anonymous constraints

   Doug McNaught <doug@wireboard.com> — 2002-07-17T14:09:01Z

   Ben Liblit <liblit@eecs.berkeley.edu> writes:
   
   > Tom Lane wrote:
   > > Try psql's \d command to check out constraint names.
   > 
   > That did it.  Thank you for the speedy reply.
   > 
   > (I can't help but shake my head at the design of ALTER TABLE's constraint
   > manipulation facilities, whose non-orthogonality requires one to step
   > outside the language and use things like "\d" to accomplish this sort of
   > task.  Perhaps when I have more database experience under my belt that
   > will feel like less of a kludge.)
   
   '\d' and friends in psql are just shorthand for queries against the
   system catalogs.  So you're not "stepping outside the language",
   really.  
   
   If you do 'psql -E' you can see the queries generated by the various
   backslash commands.
   
   -Doug
   
   

5. Re: dropping anonymous constraints

   Ben Liblit <liblit@eecs.berkeley.edu> — 2002-07-17T19:59:42Z

   Doug McNaught wrote:
   > '\d' and friends in psql are just shorthand for queries against the
   > system catalogs.  So you're not "stepping outside the language",
   > really.
   
   But the system catalogs' names and organization are themselves 
   PostgreSQL specific.  Presumably MySQL and Oracle and the other folks 
   don't have tables named "pg_relcheck", "pg_class", and so on.  They 
   probably provide the same facilities, but not in the same way.
   
   I guess this is the part I find suprising: the non-standardization of 
   database meta-information.  SQL is great for getting information *out* 
   of a database, but seems to have a serious blind spot when it comes to 
   fetching information *about* a database.
   
   {shrug}
   
   In any case, thanks for the "psql -E" tip!
   
   
   

6. Re: dropping anonymous constraints

   Neil Conway <nconway@klamath.dyndns.org> — 2002-07-17T20:25:42Z

   On Wed, Jul 17, 2002 at 12:59:42PM -0700, Ben Liblit wrote:
   > I guess this is the part I find suprising: the non-standardization of 
   > database meta-information.  SQL is great for getting information *out* 
   > of a database, but seems to have a serious blind spot when it comes to 
   > fetching information *about* a database.
   
   No, there are INFORMATION_SCHEMA views defined by SQL -- someone will
   get around to implementing them one of these days.
   
   The problem here is that (a) not everyone implements the SQL standard
   views (b) the SQL standard stuff doesn't include PostgreSQL extensions,
   obviously.
   
   Cheers,
   
   Neil
   
   -- 
   Neil Conway <neilconway@rogers.com>
   PGP Key ID: DB3C29FC