Thread: temporarily disabling foreign keys
Hi, I'm not sure how best to handle this situation. The tables in a database need to be completely cleared and copy'ed into about every few months, as they are updated with new records and corrections from a provider. Because the tables have foreign key constraints with 'ON UPDATE CASCADE ON DELETE RESTRICT', it's not possible to DELETE them without violating these constraints. I've been dropping the constraints while doing these transactions and then recreating them once everything is done, but this has gotten tedious. Is there some way to "disable" the foreign keys and enabling them after the transactions? Thanks. Cheers, -- Seb
in recent versions of postgres, there is a "replication" mode designed specifically for replication software to disable FK's and other triggers. Perhaps investigate that. the other option is to make your FK's deferrable, and do all your delete in one big transaction with the FK checks deferred. not sure if that will help. either that, or order your deletes and loads in a way such that the FKs remain satisfied. unless you have circular dependencies, this should be possible.
On 05/10/11 11:12 AM, Seb wrote: > Hi, > > I'm not sure how best to handle this situation. The tables in a > database need to be completely cleared and copy'ed into about every few > months, as they are updated with new records and corrections from a > provider. Because the tables have foreign key constraints with 'ON > UPDATE CASCADE ON DELETE RESTRICT', it's not possible to DELETE them > without violating these constraints. I've been dropping the constraints > while doing these transactions and then recreating them once everything > is done, but this has gotten tedious. Is there some way to "disable" > the foreign keys and enabling them after the transactions? Thanks. might be faster to just drop the tables and recreate them.
On Tue, May 10, 2011 at 12:12 PM, Seb <spluque@gmail.com> wrote: > Hi, > > I'm not sure how best to handle this situation. The tables in a > database need to be completely cleared and copy'ed into about every few > months, as they are updated with new records and corrections from a > provider. Because the tables have foreign key constraints with 'ON > UPDATE CASCADE ON DELETE RESTRICT', it's not possible to DELETE them > without violating these constraints. I've been dropping the constraints > while doing these transactions and then recreating them once everything > is done, but this has gotten tedious. Is there some way to "disable" > the foreign keys and enabling them after the transactions? Thanks. Another option is to load the data into temp tables and run updates from there against the master table. Delete anything that doesn't match for the rest.
On Tue, 10 May 2011 14:29:48 -0400, Vick Khera <vivek@khera.org> wrote: > in recent versions of postgres, there is a "replication" mode designed > specifically for replication software to disable FK's and other > triggers. Perhaps investigate that. > the other option is to make your FK's deferrable, and do all your > delete in one big transaction with the FK checks deferred. not sure > if that will help. > either that, or order your deletes and loads in a way such that the > FKs remain satisfied. unless you have circular dependencies, this > should be possible. Excellent, this last suggestion was actually a very simple and efficient solution. However, I got curious about the deferrable FK's, which might make this and other scripts more flexible. Thanks everyone for your feedback, -- Seb