Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f06KPt701555 for ; Sat, 6 Jan 2001 15:25:55 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f03H7t701537 for ; Wed, 3 Jan 2001 12:07:55 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id LAA30397 for pgsql-sql@postgresql.org; Wed, 3 Jan 2001 11:47:07 -0500 (EST) (envelope-from news) X-Authentication-Warning: news.tht.net: news set sender to using -f Message-ID: <3A5356A8.2568E6F6@mohawksoft.com> From: mlw X-Mailer: Mozilla 4.75 [en] (X11; U; Linux 2.2.16-3smp i686) X-Accept-Language: en MIME-Version: 1.0 X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Removing a constraint? References: <01C07414.F08156B0.mdavis@sevainc.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 31 X-Complaints-To: abuse@usenetserver.com X-Abuse-Info: Please be sure to forward a copy of ALL headers X-Abuse-Info: Otherwise we will be unable to process your complaint properly. X-Complaints-To: abuse@webusenet.com Organization: WebUseNet Corp http://www.usenetserver.com - Home of the fastest NNTP servers on the Net. Date: Wed, 03 Jan 2001 11:43:20 -0500 To: pgsql-sql@postgresql.org X-Archive-Number: 200101/70 X-Sequence-Number: 731 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