Thread: dropping foreign keys

dropping foreign keys

From
Joseph Shraibman
Date:
OK I asked this a while ago but I'll try again.  Is there any way to get rid of a foreign
key?  Deleting the entry from pg_trigger causes the database to throw errors right and left.

I'm using 7.1.3



--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://xis.xtenit.com


Re: dropping foreign keys

From
Stephan Szabo
Date:
On Wed, 12 Dec 2001, Joseph Shraibman wrote:

> OK I asked this a while ago but I'll try again.  Is there any way to get rid of a foreign
> key?  Deleting the entry from pg_trigger causes the database to throw errors right and left.
>
> I'm using 7.1.3

You should use drop trigger (For all 3 triggers - remember to quote the
name too, because it's mixed case) rather than deleting directly from
pg_trigger.

If you're already in the state where pg_class has the wrong number of
triggers, you should probably be able to correct this using a varient
of the reenable triggers code pg_dump gives for data only dumps.


Re: dropping foreign keys

From
Stephan Szabo
Date:
On Thu, 13 Dec 2001 jks@selectacast.net wrote:

> I tried that, but it didn't work.  I need to drop trigger <name> on
> <table>.  I got the name from the pg_triggers table, but apparently they
> are not associated with any table.

Two should be associated with the pktable and one with the fktable.  I've
done the drop trigger thing in the past, so unless someone's changed
something it should work.  The table names can be grabbed out of the
args, I think generally the first trigger is the one on the fktable
and the other two are the pktable ones.



Re: dropping foreign keys

From
jks@selectacast.net
Date:
I tried that, but it didn't work.  I need to drop trigger <name> on
<table>.  I got the name from the pg_triggers table, but apparently they
are not associated with any table.

On Wed, 12 Dec 2001, Stephan Szabo wrote:

>
> On Wed, 12 Dec 2001, Joseph Shraibman wrote:
>
> > OK I asked this a while ago but I'll try again.  Is there any way to get rid of a foreign
> > key?  Deleting the entry from pg_trigger causes the database to throw errors right and left.
> >
> > I'm using 7.1.3
>
> You should use drop trigger (For all 3 triggers - remember to quote the
> name too, because it's mixed case) rather than deleting directly from
> pg_trigger.
>
> If you're already in the state where pg_class has the wrong number of
> triggers, you should probably be able to correct this using a varient
> of the reenable triggers code pg_dump gives for data only dumps.
>


Re: dropping foreign keys

From
Joseph Shraibman
Date:
What is the fktable and pktable?  I tried 'fktable' and 'pktable' and neither of them worked.

And should I use the tgconstrname or tgname?  I'm using tgconstrname.

Stephan Szabo wrote:

> On Thu, 13 Dec 2001 jks@selectacast.net wrote:
>
>
>>I tried that, but it didn't work.  I need to drop trigger <name> on
>><table>.  I got the name from the pg_triggers table, but apparently they
>>are not associated with any table.
>>
>
> Two should be associated with the pktable and one with the fktable.  I've
> done the drop trigger thing in the past, so unless someone's changed
> something it should work.  The table names can be grabbed out of the
> args, I think generally the first trigger is the one on the fktable
> and the other two are the pktable ones.
>
>


--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://xis.xtenit.com


Re: dropping foreign keys

From
Stephan Szabo
Date:
On Thu, 13 Dec 2001, Joseph Shraibman wrote:

> What is the fktable and pktable?  I tried 'fktable' and 'pktable' and
> neither of them worked.

The table the foreign key is on and the table it references, sorry, I've
gotten used to using that shorthand.

> And should I use the tgconstrname or tgname?  I'm using tgconstrname.

Ah, that'd do it.  You need to use the tgname, tgconstrname was the name
given to the actual constraint, but the name for the trigger is different.
(In 7.2 if you were using drop constraint, you'd want to use
tgconstrname).



Re: dropping foreign keys

From
Joseph Shraibman
Date:

Stephan Szabo wrote:

> On Thu, 13 Dec 2001, Joseph Shraibman wrote:
>
>
>>What is the fktable and pktable?  I tried 'fktable' and 'pktable' and
>>neither of them worked.
>>
>
> The table the foreign key is on and the table it references, sorry, I've
> gotten used to using that shorthand.
>
>
>>And should I use the tgconstrname or tgname?  I'm using tgconstrname.
>>
>
> Ah, that'd do it.  You need to use the tgname


I tried both table names, and in both cases I got :
drop trigger RI_ConstraintTrigger_569229 on utable;
ERROR:  DropTrigger: there is no trigger ri_constrainttrigger_569229 on relation utable




--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://xis.xtenit.com


Re: dropping foreign keys

From
Stephan Szabo
Date:
> I tried both table names, and in both cases I got :
> drop trigger RI_ConstraintTrigger_569229 on utable;
> ERROR:  DropTrigger: there is no trigger ri_constrainttrigger_569229 on relation utable

You'll need to double quote the name because it's actually mixed case
(note the case folded name in the error message)


Re: dropping foreign keys

From
Joseph Shraibman
Date:
OK that worked. Thanks for your help.

Stephan Szabo wrote:

>>I tried both table names, and in both cases I got :
>>drop trigger RI_ConstraintTrigger_569229 on utable;
>>ERROR:  DropTrigger: there is no trigger ri_constrainttrigger_569229 on relation utable
>>
>
> You'll need to double quote the name because it's actually mixed case
> (note the case folded name in the error message)
>


--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://xis.xtenit.com