Postgres Pro
Downloads
Home   >   mailing lists

Thread: Alter table to "on update cascade"

Alter table to "on update cascade"

From
Aram Fingal
Date:
I have a table where I should have declared a foreign key with ON UPDATE CASCADE and didn't.  Now I want to fix that.
Fromthe documentation on www.postgresql.org, about ALTER TABLE it's not at all clear how to do this or even whether you
cando this.   

-Aram

Re: Alter table to "on update cascade"

From
Adrian Klaver
Date:
On 11/17/2010 08:32 AM, Aram Fingal wrote:
> I have a table where I should have declared a foreign key with ON UPDATE CASCADE and didn't.  Now I want to fix that.
From the documentation on www.postgresql.org, about ALTER TABLE it's not at all clear how to do this or even whether
youcan do this. 
>
> -Aram

http://www.postgresql.org/docs/9.0/interactive/sql-altertable.html

ADD table_constraint

     This form adds a new constraint to a table using the same syntax as
CREATE TABLE.

--
Adrian Klaver
adrian.klaver@gmail.com

Re: Alter table to "on update cascade"

From
David Fetter
Date:
On Wed, Nov 17, 2010 at 11:32:32AM -0500, Aram Fingal wrote:
> I have a table where I should have declared a foreign key with ON
> UPDATE CASCADE and didn't.  Now I want to fix that.  From the
> documentation on www.postgresql.org, about ALTER TABLE it's not at
> all clear how to do this or even whether you can do this.

You can do it like this:

BEGIN;
ALTER TABLE foo DROP CONSTRAINT your_constraint;
ALTER TABLE foo ADD FOREIGN KEY ...;
COMMIT;

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Re: Alter table to "on update cascade"

From
Richard Broersma
Date:
On Wed, Nov 17, 2010 at 8:43 AM, David Fetter <david@fetter.org> wrote:

> You can do it like this:
>
> BEGIN;
> ALTER TABLE foo DROP CONSTRAINT your_constraint;
> ALTER TABLE foo ADD FOREIGN KEY ...;
> COMMIT;

The nice thing about the ALTER TABLE statement is that you can do it
in one command:


ALTER TABLE foo
DROP CONSTRAINT your_constraint,
 ADD CONSTRAINT your_constraint FOREIGN KEY ...
              ON UPDATE CASCADE ON DELETE RESTRICT;


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Re: Alter table to "on update cascade"

From
Andreas Kretschmer
Date:
Richard Broersma <richard.broersma@gmail.com> wrote:

> On Wed, Nov 17, 2010 at 8:43 AM, David Fetter <david@fetter.org> wrote:
>
> > You can do it like this:
> >
> > BEGIN;
> > ALTER TABLE foo DROP CONSTRAINT your_constraint;
> > ALTER TABLE foo ADD FOREIGN KEY ...;
> > COMMIT;
>
> The nice thing about the ALTER TABLE statement is that you can do it
> in one command:
>
>
> ALTER TABLE foo
> DROP CONSTRAINT your_constraint,
>  ADD CONSTRAINT your_constraint FOREIGN KEY ...
>               ON UPDATE CASCADE ON DELETE RESTRICT;

yeah, cool ;-)


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Alter table to "on update cascade"

From
Aram Fingal
Date:
On Nov 17, 2010, at 12:42 PM, Richard Broersma wrote:

> ALTER TABLE foo
> DROP CONSTRAINT your_constraint,
> ADD CONSTRAINT your_constraint FOREIGN KEY ...
>              ON UPDATE CASCADE ON DELETE RESTRICT;

Thanks.  That worked.
-Aram