Re: 7.4 - TODO : alter table drop foreign key - Mailing list pgsql-hackers

From Dan Langille
Subject Re: 7.4 - TODO : alter table drop foreign key
Date
Msg-id 3DEF3DDC.26234.68927E5@localhost
Whole thread Raw
In response to Re: 7.4 - TODO : alter table drop foreign key  ("Dan Langille" <dan@langille.org>)
Responses Re: 7.4 - TODO : alter table drop foreign key
List pgsql-hackers
On 5 Dec 2002 at 11:47, Dan Langille wrote:

> On 5 Dec 2002 at 8:44, Stephan Szabo wrote:
> 
> > On Thu, 5 Dec 2002, Dan Langille wrote:
> > 
> > > On 5 Dec 2002 at 8:20, Stephan Szabo wrote:
> > >
> > > >
> > > > On Thu, 5 Dec 2002, Dan Langille wrote:
> > > >
> > > > > We support "alter table add foreign key".  How about supporting
> > > > > "alter table drop foreign key"?
> > > > >
> > > > > - he said as he went to drop a foreign key
> > > >
> > > > It seems to work for me on my 7.3b2 system with
> > > > alter table <table> drop constraint <constraint name>;
> > >
> > > Premature send.. sorry
> > >
> > > How was that FK added?  How did you determine the constraint name?
> > 
> > alter table <table> add constraint <name> foreign key ...
> > 
> > > How would you do that if the FK was added with the following syntax?
> > >
> > > alter table <table>
> > >     add foreign key (<column>)
> > >        references <othertable> (<othercolumn>)
> > > on update cascade on delete cascade;
> > 
> > IIRC, the constraint will get an automatic name of the form
> > $<n> in such cases.  I believe if you do a \d on the table,
> > it gives the name in the constraint definitions (on one of mine
> > i get:
> > 
> > Foreign Key constraints: $1 FOREIGN KEY (a) REFERENCES qqq(a) ON UPDATE
> > CASCADE ON DELETE NO ACTION
> > 
> > Where $1 is the name of the constraint.
> 
> Thanks.  In my 7.2.3 database, the table in question has:
> 
> Primary key: watch_list_staging_pkey
> Check constraints: "watch_list_stag_from_watch_list" 
> ((from_watch_list = 't'::bool) OR (from_watch_list = 'f'::bool))
>                    "watch_list_stagin_from_pkg_info" ((from_pkg_info 
> = 't'::bool) OR (from_pkg_info = 'f'::bool))
> Triggers: RI_ConstraintTrigger_4278482,
>           RI_ConstraintTrigger_4278488
> 
> No mention of FK constraints.

Found the solution:

drop trigger "RI_ConstraintTrigger_4278488" on watch_list_staging;

Given that the FK in question did not have a name to start with, I 
concede that it would be difficult to code DROP FOREIGN KEY.

What about supporting ALTER TABLE <table> ADD FOREIGN KEY <keyname> 
... which at present we don't?  That would then make dropping the FK 
a simple coding issue?
-- 
Dan Langille : http://www.langille.org/



pgsql-hackers by date:

Previous
From: "Dan Langille"
Date:
Subject: Re: 7.4 - TODO : alter table drop foreign key
Next
From: Lamar Owen
Date:
Subject: Re: [GENERAL] PostgreSQL Global Development Group