Thread: How do I drop or change a foreign key?

After I created my tables, I went back and added foreign keys to them.  Now I
need to change one of the foreign keys to 'ON DELETE CASCADE' behaviour so that
related records are automatically deleted, rather than causing an error when a
record in the parent table is deleted.  I know the name of the foreign key, but
none of the standard 'drop' commands seem to apply to it.  Am I forced to move
the data to a temp table, drop the table and recreate, then move the data back?
Improvements to the ALTER commands are already high on my personal list of
desired improvements; I run into this sort of thing fairly consistently as users
change their minds or clarify their requirements.

Thanks,

Wes Sheldahl

On Thu, 23 Aug 2001 wsheldah@lexmark.com wrote:

>
>
> After I created my tables, I went back and added foreign keys to them.  Now I
> need to change one of the foreign keys to 'ON DELETE CASCADE' behaviour so that
> related records are automatically deleted, rather than causing an error when a
> record in the parent table is deleted.  I know the name of the foreign key, but
> none of the standard 'drop' commands seem to apply to it.  Am I forced to move
> the data to a temp table, drop the table and recreate, then move the data back?
> Improvements to the ALTER commands are already high on my personal list of
> desired improvements; I run into this sort of thing fairly consistently as users
> change their minds or clarify their requirements.

I believe current source have a DROP CONSTRAINT, however for older
versions, you need to look up the trigger names for the constraint in
pg_trigger and use DROP TRIGGER (you'll need to double quote the name
because of the mixed case).

Thanks; that's just what I needed.  I'm glad you mentioned needing to
double-quote the trigger names!

Just in case anyone else needs to do this before they get DROP CONSTRAINT
support, I got the trigger names with:

select tgname, tgconstrname from pg_trigger where
tgconstrname='name_of_my_foreign_key';

and then did a DROP TRIGGER "triggername" on tablename; for each of them.
Knowing which of the two tables the trigger applied to was trial and error, but
not bad at all since only two tables were involved and there were just three
triggers.





Stephan Szabo <sszabo%megazone23.bigpanda.com@interlock.lexmark.com> on
08/23/2001 12:22:42 PM

To:   "Wesley_Sheldahl/Lex/Lexmark.LEXMARK"@sweeper.lex.lexmark.com
cc:   pgsql-general%postgresql.org@interlock.lexmark.com (bcc: Wesley
      Sheldahl/Lex/Lexmark)
Subject:  Re: [GENERAL] How do I drop or change a foreign key?



On Thu, 23 Aug 2001 wsheldah@lexmark.com wrote:

>
>
> After I created my tables, I went back and added foreign keys to them.  Now I
> need to change one of the foreign keys to 'ON DELETE CASCADE' behaviour so
that
> related records are automatically deleted, rather than causing an error when a
> record in the parent table is deleted.  I know the name of the foreign key,
but
> none of the standard 'drop' commands seem to apply to it.  Am I forced to move
> the data to a temp table, drop the table and recreate, then move the data
back?
> Improvements to the ALTER commands are already high on my personal list of
> desired improvements; I run into this sort of thing fairly consistently as
users
> change their minds or clarify their requirements.

I believe current source have a DROP CONSTRAINT, however for older
versions, you need to look up the trigger names for the constraint in
pg_trigger and use DROP TRIGGER (you'll need to double quote the name
because of the mixed case).


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org