Thread: Dropping Foreign Key without recreating table
I wanted to change a foreign key to be deferrable (db version 7.2.1). During table creation I didn't specify a constraint name for the foreign key. "\d" shows a trigger RI_ConstraintTrigger_17195, however when I'm trying to "alter table mytable drop constraint RI_ConstraintTrigger_17195 restrict" I'm getting the error "constraint does not exist. What am I doing wrong here? Or is there another simple solution to switch foreign keys to be deferrable? Regards Egon
since name of constrauint is in mixed cased u must double quote it in command. below will work. psql> alter table mytable drop constraint "RI_ConstraintTrigger_17195" restrict ; > I wanted to change a foreign key to be deferrable (db version 7.2.1). During table creation I > didn't specify a constraint name for the foreign key. > "\d" shows a trigger RI_ConstraintTrigger_17195, however when I'm trying to "alter table > mytable drop constraint RI_ConstraintTrigger_17195 restrict" I'm getting the error "constraint > does not exist. > What am I doing wrong here? Or is there another simple solution to switch foreign keys to be > deferrable? > > Regards > > Egon > > > > ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off > all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/
Oops disregard my prev reply, "RI_ConstraintTrigger_17195" is a trigger not contraint so u must in 7.2.1 do DROP TRIGGER "RI_ConstraintTrigger_17195" on mytable ; in 7.3 foreign key constraints on tables have name. so you need not drop underlying triggers like in 721 but can use command to drop the contraint which u are attempting now like alter table mytable drop constraint "NAME OF THE FKEY CONSTRAINT" restrict ; cheers mallah. > I wanted to change a foreign key to be deferrable (db version 7.2.1). During table creation I > didn't specify a constraint name for the foreign key. > "\d" shows a trigger RI_ConstraintTrigger_17195, however when I'm trying to "alter table > mytable drop constraint RI_ConstraintTrigger_17195 restrict" I'm getting the error "constraint > does not exist. > What am I doing wrong here? Or is there another simple solution to switch foreign keys to be > deferrable? > > Regards > > Egon > > > > ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off > all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/
Thanks Mallah, I didn't realize the name of the trigger is in mixed case. However, I had to drop another 2 triggers on the referenced table. So it looks for me, a foreign key uses 3 triggers at all. Looking into pg_trigger, I found them all. Thanks Egon mallah@trade-india.com wrote: > Oops disregard my prev reply, > > "RI_ConstraintTrigger_17195" is a trigger not contraint so u must in 7.2.1 do > DROP TRIGGER "RI_ConstraintTrigger_17195" on mytable ; > > in 7.3 foreign key constraints on tables have name. > so you need not drop underlying triggers like in 721 but can use command to > drop the contraint which u are attempting now like > alter table mytable drop constraint "NAME OF THE FKEY CONSTRAINT" restrict ; > > cheers > mallah. > > > I wanted to change a foreign key to be deferrable (db version 7.2.1). During table creation I > > didn't specify a constraint name for the foreign key. > > "\d" shows a trigger RI_ConstraintTrigger_17195, however when I'm trying to "alter table > > mytable drop constraint RI_ConstraintTrigger_17195 restrict" I'm getting the error "constraint > > does not exist. > > What am I doing wrong here? Or is there another simple solution to switch foreign keys to be > > deferrable? > > > > Regards > > > > Egon > > > > > > > > ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off > > all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > ----------------------------------------- > Get your free web based email at trade-india.com. > "India's Leading B2B eMarketplace.!" > http://www.trade-india.com/