Thread: ALTER TABLE table DROP CONSTRAINT problem

I have a strange problem on 7.2.1

I am trying to drop a constraint that was set up by this SQL
ALTER TABLE customer_main ADD CONSTRAINT cust_main_agent
  FOREIGN KEY ( agent )
   REFERENCES customer_main ( id )
    NOT DEFERRABLE;

So I do this:
ALTER TABLE customer_main DROP CONSTRAINT cust_main_agent RESTRICT;
And get this error:
ERROR:  ALTER TABLE / DROP CONSTRAINT: cust_main_agent does not exist

So I try to violate the constraint and I get this:
ERROR:  cust_main_agent referential integrity violation - key referenced from
customer_main not found in customer_main

Any ideas? I am wondering if postgres doesn't like the fact that I have a
constraint going from one column to the id (serial) column of the same table.
--
Lewis Bergman
Texas Communications
4309 Maple St.
Abilene, TX 79602-8044
915-695-6962 ext 115

Lewis Bergman <lbergman@[nospam]abi.tconline.net> writes:
> I am trying to drop a constraint that was set up by this SQL
> ALTER TABLE customer_main ADD CONSTRAINT cust_main_agent
>   FOREIGN KEY ( agent )
>    REFERENCES customer_main ( id )
>     NOT DEFERRABLE;

> So I do this:
> ALTER TABLE customer_main DROP CONSTRAINT cust_main_agent RESTRICT;
> And get this error:
> ERROR:  ALTER TABLE / DROP CONSTRAINT: cust_main_agent does not exist

AFAIK, the current implementation of DROP CONSTRAINT only supports
dropping CHECK constraints; it gives you that error because it doesn't
find anything about cust_main_agent in the check-constraint table.

You can manually drop a foreign-key constraint by dropping the three or
so triggers that implement it.  I forget the details but they're in the
list archives.

There will probably be a cleaner way to do it in a release or two...

            regards, tom lane