Date: Thu, 12 Dec 2002 09:11:08 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Tim Perdue <tim@perdue.net>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: ON DELETE CASCADE
In-Reply-To: <3DF8C0D7.7060907@perdue.net>
Message-ID: <20021212090519.A8841-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On Thu, 12 Dec 2002, Tim Perdue wrote:

> I'm trying to comb through my database and add ON DELETE CASCADE to a
> number of tables where I already have fkeys in place, but I'm having a
> hard time.
>
> ALTER TABLE project_task DROP CONSTRAINT
> "project_task_group_project_id_f" RESTRICT;
>
> ERROR:  ALTER TABLE / DROP CONSTRAINT: project_task_group_project_id_f
> does not exist

I think this works in 7.3, but that's the first version in which it does.

> ALTER TABLE project_task
> 	ADD CONSTRAINT projecttask_groupprojectid_fk
> 	FOREIGN KEY (group_project_id)
> 	REFERENCES project_group_list(group_project_id) ON DELETE CASCADE;
> NOTICE:  ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ALTER
>
> That command works, but now I think I have 2x as many triggers as I
> want. How do I get rid of the original triggers?

You'll need to look at pg_trigger and find the triggers associated with
the constraints.  I'd suggest removing both sets and then using alter
table again because then you can look for triggers that have the correct
tables and columns listed in tgargs. As a note there are two triggers on
project_group_list for each constraint as well and you'll need to remove
those too.