Обсуждение: dropping foreign keys
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
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.
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.
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. >
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
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).
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
> 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)
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