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


Jeremy wrote:
In article <1142612664.419458.147650@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,
fitzjarrell@xxxxxxx says...
Commens embedded.
laredotornado@xxxxxxxxxxx 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

.

Relevant Pages

  • Re: How do you set a value to Null in a DataSet when its normally set to another table through a rel
    ... If it doesn't reference the other table at all it's an Orphan which is what ... You can try disabling the constraints but I ...
    (microsoft.public.dotnet.languages.vb)
  • DataTable ConstraintException problem/debugging VB - VS 2005
    ... I am loading a Typed Dataset using another dataset via each table's LoadMethod and getting a ConstraintException on one of the tables. ... Disabling the constraints before the load and Enabling constraints after the load produces the same error. ... I have tried eyeballing each column's constraints in the Properties window while in the DataSet Designer and the datatable rows thru the visualizer to no avail. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Best practice for loading data into relational tables
    ... Or use deferrable constraints which would likely be better than ... I think I got a lot of viable solutions, I did not know about Toad ... But I think what I am planning to do is a combination of disabling ... from) into a shell script with debugging info. ...
    (comp.databases.oracle.misc)
  • Re: Delete query taking long time to execute
    ... This is because of the multiple relationship. ... constraints to make the DELETE faster. ... There are of course two downsides to disabling the constraints: ... constraints for considering shortcuts for the query plan, ...
    (microsoft.public.sqlserver.mseq)
  • Re: Foreign Key Deletion problem
    ... Disabling all constraints would include primary key constraints, ... Disabling all constraints would not be a good thing. ... >>Try disable all triggers: ...
    (comp.databases.oracle.server)