Thread
-
ON DELETE CASCADE
Tim Perdue <tim@perdue.net> — 2002-12-12T17:01:11Z
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 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? alexandria=# \d project_task Table "project_task" Column | Type | Modifiers ------------------+------------------+----------------------- project_task_id | integer | not null default nextval('project_task_pk_seq'::text) group_project_id | integer | not null default '0' summary | text | not null default '' details | text | not null default '' percent_complete | integer | not null default '0' priority | integer | not null default '0' hours | double precision | not null default '0.00' start_date | integer | not null default '0' end_date | integer | not null default '0' created_by | integer | not null default '0' status_id | integer | not null default '0' Indexes: projecttask_projid_status Primary key: project_task_pkey Triggers: RI_ConstraintTrigger_51030049, RI_ConstraintTrigger_51030047, RI_ConstraintTrigger_4305858, RI_ConstraintTrigger_4305852, RI_ConstraintTrigger_4305846 After adding the new foreign key: Triggers: RI_ConstraintTrigger_51364957, ***new RI_ConstraintTrigger_51030049, RI_ConstraintTrigger_51030047, RI_ConstraintTrigger_4305858, RI_ConstraintTrigger_4305852, RI_ConstraintTrigger_4305846 -
Re: ON DELETE CASCADE
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-12-12T17:11:08Z
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.
-
Re: ON DELETE CASCADE
Marie G. Tuite <marie.tuite@edisonaffiliates.com> — 2002-12-12T17:26:24Z
Here is a function that I use to list fk(triggers) on a table. Execute the function to get the trigger name and then - drop trigger "trigger_name" on table_name; hth -- Version 1.0, June 2002 -- Marie G. Tuite -- Function lists FK by table. -- To execute: -- begin;select fn_list_fk('table_name'); -- fetch all from "<cursor_name>"; end; create or replace function fn_list_fk(name) returns refcursor as ' declare table_in alias for $1; rc refcursor; begin open rc for select tgname as trigger_name_sys ,tgconstrname as trigger_name_given ,b.relname as table_name ,tgisconstraint as ri ,c.relname as parent_table from pg_trigger a, pg_class b, pg_class c where a.tgrelid=b.oid and b.relname=table_in and tgconstrrelid = c.oid; return rc; end; ' language 'plpgsql' ; > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Tim Perdue > Sent: Thursday, December 12, 2002 11:01 AM > To: pgsql-sql@postgresql.org > Subject: [SQL] ON DELETE CASCADE > > > 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 > > 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? > > > alexandria=# \d project_task > Table "project_task" > Column | Type | Modifiers > ------------------+------------------+----------------------- > project_task_id | integer | not null default > nextval('project_task_pk_seq'::text) > group_project_id | integer | not null default '0' > summary | text | not null default '' > details | text | not null default '' > percent_complete | integer | not null default '0' > priority | integer | not null default '0' > hours | double precision | not null default '0.00' > start_date | integer | not null default '0' > end_date | integer | not null default '0' > created_by | integer | not null default '0' > status_id | integer | not null default '0' > Indexes: projecttask_projid_status > Primary key: project_task_pkey > Triggers: RI_ConstraintTrigger_51030049, > RI_ConstraintTrigger_51030047, > RI_ConstraintTrigger_4305858, > RI_ConstraintTrigger_4305852, > RI_ConstraintTrigger_4305846 > > After adding the new foreign key: > > Triggers: RI_ConstraintTrigger_51364957, ***new > RI_ConstraintTrigger_51030049, > RI_ConstraintTrigger_51030047, > RI_ConstraintTrigger_4305858, > RI_ConstraintTrigger_4305852, > RI_ConstraintTrigger_4305846 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -
Re: ON DELETE CASCADE
Dan Langille <dan@langille.org> — 2002-12-12T18:17:28Z
On 12 Dec 2002 at 11:01, Tim Perdue wrote: > 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? I had to do something similar and documented it at http://www.freebsddiary.org/postgresql-dropping-constraints.php hth -- Dan Langille : http://www.langille.org/
-
Re: ON DELETE CASCADE
Tim Perdue <tim@perdue.net> — 2002-12-16T02:29:40Z
Marie G. Tuite wrote: > Here is a function that I use to list fk(triggers) on a table. Execute the > function to get the trigger name and then - drop trigger "trigger_name" on > table_name; Thanks for the function. Strangely enough, it appears the "extra" triggers are gone all by themselves. It seems postgres appropriately deletes or updates the old triggers when you alter the table the second time, even if you did not drop the prior triggers. >>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 >> >>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?