Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    67

    Unanswered: Ignore foreign keys restrictions

    Hi.

    I have a script that makes some insert (a lot!).
    But the problem is that they aren't in the right order.
    So I get errors from Oracle saying "integrity constraint (xxx) violated".
    Is there any way to ignore them? (just during this script).

    Thanks

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    alter table <table_name> disable constraint <constraint_name>

    This will disable it for everyone, though.
    -cf

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Or define the constraints as DEFERRABLE INITIALLY IMMEDIATE, and then you cna do this:

    SET CONSTRAINTS ALL DEFERRED;
    -- Insert all data (constraints will not fire)
    COMMIT;
    -- Constraints get checked now

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Andrew,
    What happens if you violate a constraint in your scenario, since the 'commit' triggers the check? Does the commit not occur?
    -cf

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by chuck_forbes
    Andrew,
    It's Tony actually
    Quote Originally Posted by chuck_forbes
    What happens if you violate a constraint in your scenario, since the 'commit' triggers the check? Does the commit not occur?
    That's right, the inserts get rolled back with an error:
    Code:
    SQL> commit;
    commit
    *
    ERROR at line 1:
    ORA-02091: transaction rolled back
    ORA-00001: unique constraint (SCOTT.EMP_PK) violated
    I can see how that could be a problem in the large dataload situation! So your solution (disable/enable) is preferable here.

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    You could've called me 'Forbes'
    -cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •