Received: from rakete.joerdens.de ([62.104.116.200]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f28Fgpx43901 for ; Thu, 8 Mar 2001 10:42:51 -0500 (EST) (envelope-from frank@rakete.joerdens.de) Received: (from frank@localhost) by rakete.joerdens.de (8.9.3/8.9.3) id QAA16616 for pgsql-sql@postgresql.org; Thu, 8 Mar 2001 16:42:47 +0100 Date: Thu, 8 Mar 2001 16:42:46 +0100 From: Frank Joerdens To: pgsql-sql@postgresql.org Subject: How to drop an trigger Message-ID: <20010308164246.A16533@rakete.joerdens.de> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.1.2i X-Subliminal-Message: Use Linux! Use Linux! X-Archive-Number: 200103/174 X-Sequence-Number: 1827 I've created quite a few foreign key constraints in the database that I am currently working on, and now that I've altered the structure and dropped a table that had a foreign key reference to a couple of other tables, I need to get rid of those foreign keys (they weren't dropped automagically with the table), as I get errors on trying to update those tables. Trouble is that the foreign keys show up in a schema dump as triggers (AFAIK there is no other way to display foreign key constraints) which I don't know how to drop. Here's an example: \connect - frank -- -- TOC Entry ID 56 (OID 52367) -- -- Name: "RI_ConstraintTrigger_52366" Type: TRIGGER Owner: frank -- CREATE CONSTRAINT TRIGGER "" AFTER DELETE ON "index" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_cascade_del" ('', 'legende', 'index', 'UNSPECIFIED', 'platz', 'id'); I tried dropping it with =# drop trigger RI_ConstraintTrigger_52366 on index; which fails with ERROR: DropTrigger: there is no trigger ri_constrainttrigger_52366 on relation index What to do? And more broadly, what's the recommended way to deal with this in general? Avoid creating triggers by always creating named foreign keys with something like ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses(address) MATCH FULL; (from Bruce's book)? Regards, Frank