pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Removing a constraint?
3+ messages / 3 participants
[nested] [flat]

* Removing a constraint?
@ 2001-01-02 00:04 Michael Davis <mdavis@sevainc.com>
  2001-01-02 02:40 ` Re: [SQL] Removing a constraint? Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-01-03 16:43 ` Re: Removing a constraint? mlw <markw@mohawksoft.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 00:04 Removing a constraint? Michael Davis <mdavis@sevainc.com>
@ 2001-01-02 02:40 ` Stephan Szabo <sszabo@megazone23.bigpanda.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-02 00:04 Removing a constraint? Michael Davis <mdavis@sevainc.com>
@ 2001-01-03 16:43 ` mlw <markw@mohawksoft.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