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
Import Notes
Reply to msg id not found: BenLiblit'smessageofWed17Jul2002011658-0700PDT
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