Обсуждение: 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/