altering foreign key without a table scan
От | Vincent de Phily |
---|---|
Тема | altering foreign key without a table scan |
Дата | |
Msg-id | 2313139.yUPOj5e1or@moltowork обсуждение исходный текст |
Ответы |
Re: altering foreign key without a table scan
|
Список | pgsql-general |
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). Thanks in advance. -- Vincent de Phily
В списке pgsql-general по дате отправления: