Re: RE: finding and removing a constraint...

Stephan Szabo
sszabo(at)bigpanda(dot)com

On Wed, 21 Mar 2001, Brent R. Matzelle wrote:

> 3/20/2001 6:18:23 AM, Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> wrote:
> >> The problem is that the syntax
> >>
> >> create table SomeTable (
> >> SomeColumn int4 references OtherTable ( SomeColumn ),
> >> . . .
> >>
> >> creates an <unnamed> trigger which you can't drop with drop trigger
> >> because it doesn't have a name. What you suggest does work for me, i.e.
> >> deleting from pg_trigger and then adjusting the trigger count in
> >> pg_class but it is quite cumbersome.
> >
> >You should be able to, you just have to use the *real* trigger name (first
> >column in pg_trigger, will look like RI_ConstraintTrigger_<oid> or
> >something like that) and make sure to "" it because it's a quoted mixed
> >case name.
>
> It might be even easier to just delete according to the oid.
>
> select oid, * from pg_trigger;
> delete from pg_trigger where oid = 49469 /* Numbers are easier */;

Because he has two fk constraints on the table, plus, two of the
triggers are on the referenced table not the referencing table and
deleting from pg_trigger is unsafe unless you also set reltriggers in
pg_class, it really is generally better to use drop trigger. :)