Vincent de Phily <vincent.dephily@xxxxxxxxxxxxxxxxx> writes: > Hi list, > > as part of a db schema update, I'd like to alter the "on update" property of a > fkey, for example going from : >> ALTER TABLE ONLY foo ADD CONSTRAINT foo_barid_fkey FOREIGN KEY (barid) >> REFERENCES bar(id) ON UPDATE CASCADE ON DELETE CASCADE; > to : >> ALTER TABLE ONLY foo ADD CONSTRAINT foo_barid_fkey FOREIGN KEY (barid) >> REFERENCES bar(id) ON UPDATE RESTRICT ON DELETE CASCADE; > > I understand I can create the new fkey and drop the old one, but this requires > a scan of the table (to check that no existing data violates the new fkey) > which, on this large, heavily-updated, no-downtime table I can't really aford. > > The thing is, I know there is no violation by existing data, because of the > existing fkey. So locking and scaning the table to add the "duplicate" fkey is > not necessary. In a sense, I'm looking for : >> ALTER TABLE foo ALTER CONSTRAINT foo_barid_fkey ON UPDATE RESTRICT; > I'm guessing/wishfull-thinking that some hackery with the system catalog could > emulate that ? > > I'm currently using postgres 8.3 (we want to upgrade, but it's hard to > schedule). Two things first... 1. I assume this is same for 8.3 2. Someone from Hackers best to answer if this is safe on live system or might require at least a restart. Your 2 catalog fields of interest are; pg_constraint.(confupdtype|confdeltype) Changing those for the relevant FKs should satisfy your needs. I am not aware of those field values being duplicated anywhere. Strongly suggest you approach this with caution, as is standard advice regarding any manual catalog fiddling. HTH > > Thanks in advance. > > -- > Vincent de Phily > > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@xxxxxxxxxxx p: 305.321.1144 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
