From: Marie G. Tuite <marie.tuite@edisonaffiliates.com>
To: Tim Perdue <tim@perdue.net>
To: pgsql-sql@postgresql.org
Subject: Re: ON DELETE CASCADE
Date: Thu, 12 Dec 2002 11:26:24 -0600
Message-ID: <IGELKLINGDMODABPOOFEAEACCPAA.marie.tuite@edisonaffiliates.com> (raw)
In-Reply-To: <3DF8C0D7.7060907@perdue.net>
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)
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: marie.tuite@edisonaffiliates.com, tim@perdue.net
Subject: Re: ON DELETE CASCADE
In-Reply-To: <IGELKLINGDMODABPOOFEAEACCPAA.marie.tuite@edisonaffiliates.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox