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

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