Alter table to "on update cascade"

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
adrian.klaver@gmail.com

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

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

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�

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