Обсуждение: dropping anonymous constraints

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.

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

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!

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

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!

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