Thread: Modifying a foreign key constraint?

What's the best way to modify a foreign key constraint?

I need to change a foreign key from ON DELETE NO ACTION to ON DELETE
CASCADE.  Should I just drop the constraint and re-add it?  For example:

ALTER TABLE foo DROP CONSTRAINT foo_bar_id_fkey;

ALTER TABLE foo ADD FOREIGN KEY (foo_bar_id_fkey) REFERENCES bar(id) ON
DELETE CASCADE;

Is there a more compact way to do this, perhaps with a single ALTER
TABLE command?

Thanks!

        Marc

> ALTER TABLE foo DROP CONSTRAINT foo_bar_id_fkey;
>
> ALTER TABLE foo ADD FOREIGN KEY (foo_bar_id_fkey) REFERENCES bar(id) ON
> DELETE CASCADE;
>
> Is there a more compact way to do this, perhaps with a single ALTER
> TABLE command?

Sure there is, you can preform multiple alterations in one statement:

ALTER TABLE foo DROP CONSTRAINT foo_bar_id_fkey,
                ADD CONSTRAINT foo_bar_id_fkey
                FOREIGN KEY (bar_fkey)
                REFERENCES bar (id)
                ON DELETE CASCADE;

regards,
Richard Broersma Jr.

Richard Broersma Jr wrote:
>
> Sure there is, you can preform multiple alterations in one statement:
>
> ALTER TABLE foo DROP CONSTRAINT foo_bar_id_fkey,
>                 ADD CONSTRAINT foo_bar_id_fkey
>                 FOREIGN KEY (bar_fkey)
>                 REFERENCES bar (id)
>                 ON DELETE CASCADE;

Doh!  That's perfectly fine, of course.

I was wondering if there might be something along the lines of an ALTER
CONSTRAINT clause, which could change a specific aspect of a constraint
without having to re-specify the whole thing.

        Marc

> I was wondering if there might be something along the lines of an ALTER
> CONSTRAINT clause, which could change a specific aspect of a constraint
> without having to re-specify the whole thing.

As you see from this link:
http://www.postgresql.org/docs/8.2/interactive/sql-commands.html

There is no alter constraint command.

However for the Alter table syntax this link shows pretty much all you can do:
http://www.postgresql.org/docs/8.2/interactive/sql-altertable.html

Regards,
Richard Broersma Jr.