Обсуждение: altering foreign keys
In my database, I have a core table that nearly all other tables key against. Now I need to adjust all of those foreign keys to add a "on update cascade" action. Is there a way to alter the existing keys? (it didn't jump out at me in the manual) If not, is there a serious issue preventing this feature? If I have to drop and re-create all of the foreign keys, is it possible to wrap the whole operation in a transaction without risking invalid inserts in the referring tables? (I come from an Oracle background, where DDL causes an implicit commit)
On Jan 22, 2008, at 1:11 PM, Adam Rich wrote: > In my database, I have a core table that nearly all other tables > key against. Now I need to adjust all of those foreign keys to > add a "on update cascade" action. Is there a way to alter the > existing keys? (it didn't jump out at me in the manual) > > If not, is there a serious issue preventing this feature? > > If I have to drop and re-create all of the foreign keys, is it > possible to wrap the whole operation in a transaction without > risking invalid inserts in the referring tables? (I come from > an Oracle background, where DDL causes an implicit commit) DDL in Postgres is transactional so your second option will work just fine. Erik Jones DBA | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
--- On Tue, 1/22/08, Adam Rich <adam.r@sbcglobal.net> wrote: > Is there a way to alter the > existing keys? (it didn't jump out at me in the manual) ALTER TABLE your_table DROP CONSTRAINT your_column_fkey_constraint, ADD CONSTRAINT your_column_fkey_constraint FOREIGN KEY your_column REFERENCES parent_table( primary_column ) ON UPDATE CASCADE; Regards, Richard Broersma Jr.
> In my database, I have a core table that nearly all other tables > key against. Now I need to adjust all of those foreign keys to > add a "on update cascade" action. Is there a way to alter the > existing keys? (it didn't jump out at me in the manual) > Would it be possible to modify confupdtype in pg_constraint ?