Thread: 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_taskADD CONSTRAINT projecttask_groupprojectid_fkFOREIGN 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
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.
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_triggera, 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)
>
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/
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?