Re: Modifying a foreign key constraint?

Поиск
Список
Период
Сортировка
Искать

Re: Modifying a foreign key constraint?

От:
Richard Broersma Jr <rabroersma@yahoo.com>
Дата:
> 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.

Modifying a foreign key constraint?

От:
Marc Branchaud <marcnarc@x2omedia.com>
Дата:
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

Re: Modifying a foreign key constraint?

От:
Marc Branchaud <marcnarc@x2omedia.com>
Дата:
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

Re: Modifying a foreign key constraint?

От:
Richard Broersma Jr <rabroersma@yahoo.com>
Дата:
> 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.
FAQ