Quantcast

Alter table to "on update cascade"

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Alter table to "on update cascade"

Aram Fingal
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.  

-Aram
--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Alter table to "on update cascade"

Adrian Klaver-3
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 you can 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
[hidden email]

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Alter table to "on update cascade"

David Fetter
In reply to this post by Aram Fingal
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 <[hidden email]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: [hidden email]
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Alter table to "on update cascade"

Richard Broersma
On Wed, Nov 17, 2010 at 8:43 AM, David Fetter <[hidden email]> 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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Alter table to "on update cascade"

Andreas Kretschmer-2
Richard Broersma <[hidden email]> wrote:

> On Wed, Nov 17, 2010 at 8:43 AM, David Fetter <[hidden email]> 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°

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Alter table to "on update cascade"

Aram Fingal
In reply to this post by Richard Broersma

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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Loading...