Skip site navigation (1) Skip section navigation (2)

Re: How to temporarily disable a table's FK constraints?

From: Erik Jones <erik(at)myemma(dot)com>
To: Kynn Jones <kynnjo(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: How to temporarily disable a table's FK constraints?
Date: 2007-11-05 18:45:46
Message-ID: E833ACD0-7709-4AA1-8713-90DEA7BB45AF@myemma.com (view raw or flat)
Thread:
Lists: pgsql-general
On Nov 5, 2007, at 11:52 AM, Kynn Jones wrote:

> On 11/5/07, Erik Jones <erik(at)myemma(dot)com> wrote:
>
>> On Nov 5, 2007, at 10:50 AM, Kynn Jones wrote:
>>> Is there a standard way to disable a table foreign-key constraint
>>> temporarily?
>>>
>>> I thought that this would be a fairly common thing to want to do...
>
>> Can you explain what it is you're actually trying to do?  As in,
>> what's your use case for needing to do this?
>
> A Perl script that needs to update a referring table with many new
> entries before knowing the foreign keys for each new record.  (I
> described a similar situation in a recent post, Subject: Populating
> large DB from Perl script.)

So, let me rephrase to see if I get you correctly:  you want to  
insert a lot of data and then go back and update the inserted rows  
with the foreign key values?  If that's the case and you can do all  
of that in one transaction then you should talk to your dba or  
whomever the table owner is to see about redefining the foreign key  
as being deferrable.  That will make it so that the foreign key  
constraints aren't checked until transaction commit time rather than  
at statement execution time.

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)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



In response to

Responses

pgsql-general by date

Next:From: André VolpatoDate: 2007-11-05 19:13:43
Subject: Filter sequence
Previous:From: CharlesDate: 2007-11-05 18:36:12
Subject: running postgresql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group