Re: How to disable foreign key constraints in a stored procedure?

Mladen Gogala wrote:
> On Fri, 17 Mar 2006 17:40:58 +0000, Jeremy wrote:
>
> > Suppose you want to bulk load data into database - the data is coming
> > from a source where its integrity is guaranteed (because of the
> > constaints there) and you want the quickest load time (perhaps this is a
> > data migration exercise?) - is it not reasonable to want to temporarily
> > disable constraints? Assuming that this is dedicated access for the
> > duration?
>
> In the data warehouse situation, it is unreasonable to even have the
> constraints in place. You should drop them and not disable them.

Except that they can give information to the optimizer and thus help it choose better execution plans. They are also useful for various query generation tools.

One alternative approach might be:

• Disable the constraints before the bulk load
• Load the data
• Check the results with a custom procedure (e.g. using MINUS to check for invalid foreign key values). If this finds errors, abort the load process.
• ALTER TABLE t ENABLE NOVALIDATE CONSTRAINT c;

However you would have to be very sure that the constraints and the validation code were in sync. Received on Mon Mar 20 2006 - 09:46:14 CST