Обсуждение: drop constraint problem

Hi all,

i'm running 7.2.1 and have problems with dropping constraints:

fakt32=#  alter table wkfaktzeilen           add constraint printjobfk foreign key (printjob)           references
wkfaktdruck(printjob)          match full           on delete cascade           on update cascade;
 
>ALTER

fakt32=# alter table wkfaktzeilen drop constraint printjobfk restrict;
>ERROR:  ALTER TABLE / DROP CONSTRAINT: printjobfk does not exist

fakt32=# alter table wkfaktdruck drop constraint printjobfk restrict;
>ERROR:  ALTER TABLE / DROP CONSTRAINT: printjobfk does not exist

but when dropping the table, the triggers are deleted and i can see the
entries
in pg_triggers??

please help
Wolfgang

From the reference page for ALTER TABLE:

"Currently DROP CONSTRAINT drops only CHECK constraints. To remove FOREIGN
KEY constraints you need to recreate and reload the table, using other
parameters to the CREATE TABLE command."

However, I think the following is also safe:


create table p (id serial primary key);

create table c (id int);

alter table c add constraint c_ref_p foreign key (id) references p;

select * from pg_trigger;
-- look at actual names of trigger, above, and substitute below

drop trigger "RI_ConstraintTrigger_16573" on c;
drop trigger "RI_ConstraintTrigger_16575" on p;
drop trigger "RI_ConstraintTrigger_16577" on p;


Hope this helps. Some background on playing in the RI table is at
http://techdocs.postgresql.org/techdocs/hackingreferentialintegrity.php


Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of
> Wolfgang.Fuertbauer@ebewe.com
> Sent: Tuesday, May 21, 2002 3:13 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] drop constraint problem
>
>
>
> Hi all,
>
> i'm running 7.2.1 and have problems with dropping constraints:
>
> fakt32=#  alter table wkfaktzeilen
>             add constraint printjobfk foreign key (printjob)
>             references wkfaktdruck(printjob)
>             match full
>             on delete cascade
>             on update cascade;
> >ALTER
>
> fakt32=# alter table wkfaktzeilen drop constraint printjobfk restrict;
> >ERROR:  ALTER TABLE / DROP CONSTRAINT: printjobfk does not exist
>
> fakt32=# alter table wkfaktdruck drop constraint printjobfk restrict;
> >ERROR:  ALTER TABLE / DROP CONSTRAINT: printjobfk does not exist
>
> but when dropping the table, the triggers are deleted and i can see the
> entries
> in pg_triggers??
>
> please help
> Wolfgang
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

If y;ou check the manual, you'll notice that Postgres doesn't support
dropping foreign key constraints just yet...

You'll need to delete the three constraints manually from pg_trigger.
This is tricky, but you can dlete them by their name and the oid of the
table that they're on...

Chris

On Tue, 21 May 2002 Wolfgang.Fuertbauer@ebewe.com wrote:

>
> Hi all,
>
> i'm running 7.2.1 and have problems with dropping constraints:
>
> fakt32=#  alter table wkfaktzeilen
>             add constraint printjobfk foreign key (printjob)
>             references wkfaktdruck(printjob)
>             match full
>             on delete cascade
>             on update cascade;
> >ALTER
>
> fakt32=# alter table wkfaktzeilen drop constraint printjobfk restrict;
> >ERROR:  ALTER TABLE / DROP CONSTRAINT: printjobfk does not exist
>
> fakt32=# alter table wkfaktdruck drop constraint printjobfk restrict;
> >ERROR:  ALTER TABLE / DROP CONSTRAINT: printjobfk does not exist
>
> but when dropping the table, the triggers are deleted and i can see the
> entries
> in pg_triggers??
>
> please help
> Wolfgang
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>