Re: Changing a foreign key constraint?

On Thu, 3 Apr 2003, David Fetter wrote:

> Kind people,
>
> I'd like to change a foreign key constraint, 'cause it's not doing the
> right thing right now.
>
> Example DDL's below...
>
> CREATE TABLE foo (
> foo_id SERIAL NOT NULL PRIMARY KEY
> , ...
> );
>
> CREATE TABLE bar (
> ...
> , foo_id INTEGER NOT NULL REFERENCES foo(foo_id)
> ...
> );
>
> I'd like to change that to
>
> , foo_id INTEGER NOT NULL REFERNCES foo(foo_id) ON DELETE CASCADE
>
> Is there any way to do this short of dropping & re-creating the db?
>
> Big TIA for any hints, tips or pointers on this :)

With 7.3, I think you can drop just the constraint and re-add it with the
additional thing.

something like:
ALTER TABLE bar DROP CONSTRAINT "$1";
ALTER TABLE bar ADD FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON DELETE
CASCADE;

In earlier versions, you could do this by finding and dropping the
constraint triggers for the constraint and then adding the constraint
again with alter table.