Обсуждение: 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