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

Jeremy wrote:
> In article <1142612664.419458.147650_at_p10g2000cwp.googlegroups.com>,
> fitzjarrell_at_cox.net says...
> > Commens embedded.
> > laredotornado_at_zipmail.com wrote:
> > > Hi,
> > >
> > > Is there another way to do what I'm trying to do in Oracle 9i for
> > > Solaris? In a stored procedure, I want to disable some foreign key
> > > constraints, perform some actions, and then re-enable them.
> >
> <snip>
>
> > > Your suggestions are apprecaited, - Dave
>
> You would need to use "execute immediate" as has been suggested.
>
> >
> > My suggestion would be to re-think your 'reasoning' for disabling the
> > foreign keys.
>
> 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?
>
> --
>
> jeremy

Define 'dedicated access'. Certainly you can think a late-night session after normal business hours would be 'dedicated access' but I wouldn't consider it so. Could you guarantee no other user is connected to the database? Could you guarantee no other connected user is accessing/modifying the tables in question? I couldn't, and I would not put myself into such a position. If you're suggesting a shutdown/startup restrict scenario then I could agree that no 'normal' user could be accessing/modifying data, but granting restricted session to the application account would put you back into the first situation I described. Of course you could create a 'load' user for the application and grant that account, and that application account only, restricted session and reasonably guarantee no application user is tinkering with any tables which may be loaded. That seems like a lot of work to allow referential integrity constraints to be disabled. And, how do you know that some error in data transfer hasn't occurred which might corrupt a foreign key value? Disabling the constraints would allow such data into the tables and then disallow re-enabling those constraints due to a violation. Yes, you could enable the constraints novalidate (not the best course of action to take after a data load into an already populated table) however you'd still be stuck with records having no reference all due to data errors, records which DO have a reference in the source database.

As much as you'd argue for disabling the constraints I'll argue against it. And I see absolutely no purpose in disabling foreign key constraints in a stored procedure.

David Fitzjarrell Received on Fri Mar 17 2006 - 12:51:28 CST