pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRemoving a constraint?
3+ messages / 3 participants
[nested] [flat]
* Removing a constraint?
@ 2001-01-02 00:04 Michael Davis <mdavis@sevainc.com>
0 siblings, 2 replies; 3+ messages in thread
From: Michael Davis @ 2001-01-02 00:04 UTC (permalink / raw)
To: pgsql-sql; 'pgsql-admin@postgresql.org' <pgsql-admin@postgresql.org>
Does anyone know how to completely and accurately remove or drop a
constraint, specifically a foreign key constraint? I tried to remove a
constraint by deleting it's trigger from pg_triggers. This caused some
undesirable side effects with other tables involved with the constraint. I
have several tables that I need to change the column constraints and
foreign key constraints on. Recreating (drop and create) the table every
time I need to change a column constraint is a pain because all the objects
that reference the table would also need to be recreated (i.e. views and
triggers). How do production DBAs successfully make changes to their
tables?
FYI, I was able to alter table add the same constraint many times. Is this
a problem? This created a new trigger in pg_triggers every time.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] Removing a constraint?
@ 2001-01-02 02:40 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Michael Davis <mdavis@sevainc.com>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2001-01-02 02:40 UTC (permalink / raw)
To: Michael Davis <mdavis@sevainc.com>; +Cc: pgsql-sql; 'pgsql-admin@postgresql.org' <pgsql-admin@postgresql.org>
It should work if you remove all three triggers for the constraint
using drop trigger, don't delete rows from pg_trigger unless you go
through and manually change the row in pg_class for the relation
the trigger is for.
On Mon, 1 Jan 2001, Michael Davis wrote:
> Does anyone know how to completely and accurately remove or drop a
> constraint, specifically a foreign key constraint? I tried to remove a
> constraint by deleting it's trigger from pg_triggers. This caused some
> undesirable side effects with other tables involved with the constraint. I
> have several tables that I need to change the column constraints and
> foreign key constraints on. Recreating (drop and create) the table every
> time I need to change a column constraint is a pain because all the objects
> that reference the table would also need to be recreated (i.e. views and
> triggers). How do production DBAs successfully make changes to their
> tables?
>
> FYI, I was able to alter table add the same constraint many times. Is this
> a problem? This created a new trigger in pg_triggers every time.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Removing a constraint?
@ 2001-01-03 16:43 mlw <markw@mohawksoft.com>
parent: Michael Davis <mdavis@sevainc.com>
1 sibling, 0 replies; 3+ messages in thread
From: mlw @ 2001-01-03 16:43 UTC (permalink / raw)
To: pgsql-sql
Michael Davis wrote:
> Does anyone know how to completely and accurately remove or drop a
> constraint, specifically a foreign key constraint? I tried to remove a
> constraint by deleting it's trigger from pg_triggers. This caused some
> undesirable side effects with other tables involved with the constraint. I
> have several tables that I need to change the column constraints and
> foreign key constraints on. Recreating (drop and create) the table every
> time I need to change a column constraint is a pain because all the objects
> that reference the table would also need to be recreated (i.e. views and
> triggers). How do production DBAs successfully make changes to their
> tables?
>
> FYI, I was able to alter table add the same constraint many times. Is this
> a problem? This created a new trigger in pg_triggers every time.
A bug in "cluster" will do this, if you cluster a table it will drop all
constraints and indexes.
One could use pg_dump, as:
pg_dump -a -t table database > table_data.sql
pg_dump -s -t table database > table_schema.sql
(edit table_schema.sql to remove constraint)
psql database < table_schema.sql
psql database < table_data.sql
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-01-03 16:43 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-02 00:04 Removing a constraint? Michael Davis <mdavis@sevainc.com>
2001-01-02 02:40 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-01-03 16:43 ` mlw <markw@mohawksoft.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox