Re: fk constraint can't be dropped

On Wed, 6 Mar 2002, Zhang, Anna wrote:

> Hi,
> I created a foreign key constraint on table referral like this:
>
> alter table referral add constraint fk_referral foreign key (handle)
> references domain (handle);
> create
>
> alter table referral drop constraint fk_referral restrict;
> ERROR: ALTER TABLE / DROP CONSTRAINT: fk_referral does not exist.
>
> I am using postgres 7.2. If fk can't be dropped, what the synax: ALTER TABLE
> [ ONLY ] table DROP CONSTRAINT constraint { RESTRICT | CASCADE } for in
> docs? If we have to drop fk, does this mean we have to drop table and

The man page for alter table seems fairly explicit that drop constraint
only drops check constraints currently.

> recreate without fk? Stupid!

That's the easiest way. You could also drop the three triggers that
actually are the implementation of the constraint (which you can find
by looking into pg_trigger - look for triggers that have names like
RI_ConstraintTrigger_<numbers> that have appropriate the appropriate
name in tgconstrname and use drop trigger
"RI_ConstraintTrigger_<numbers>"; for each of them)