Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    76

    Question Unanswered: Deferrable constraints

    Hi,

    I am posting this post because I'm having a dooubt about Deferrable Constraints.

    I have an Multitable insert script (with ALL parameter) that inserts data onto 2 tables that have dependency between them.
    Because the Unique Key constraints I'm using DEFERRABLE constraints in child table.

    In my Package I have an:
    Code:
    EXECUTE IMMEDIATE 'SET CONSTRAINT xpto DEFERRED';
    And after the INSERT script (or in the Exceptions area) I put them again active with the IMMEDIATE clause:

    Code:
    EXECUTE IMMEDIATE 'SET CONSTRAINTS ALL IMMEDIATE';
    My doubt is:
    When this script is running (and it takes several minutes), when I go and see the Constraints status at the table it does not shows it as DEFERRED. I visually don't see that the SET ....DEFFERED worked.
    The problem is that I'm getting an error about the constraint. I guess that it is only when I "activate" it again but I'm confused not to se its status chenged while the process is running.

    Is this supposte to happen this way?

    Thanks,
    Master Aucrun

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    Intead of making vague allusions as to what is happening, post what is actually happening. Post the code and the errors.

  3. #3
    Join Date
    Dec 2003
    Posts
    76
    Hi,

    I'm not at work (it is almost midnight in here) so I cannot reproduce the error code exactly. I only know that the error is something about INTEGRITY CONSTRAINT ... with the constraint DEFFERED.

    But my question was another.

    Somewhere in the begining of my Package I have the command:
    EXECUTE IMMEDIATE 'SET CONSTRAINT xpto DEFERRED';
    But when my Package is executing and I go to the table Definitions that constraint only appears as DEFERRABLE but not DEFFERED. I'm in doubt if the contraint is actually DEFFERED with my EXECUTE IMMEDIATE.

    How can I confirm if the constraint is actually DEFFERED?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >The problem is that I'm getting an error about the constraint.
    ERROR?
    What Error?
    I don't see any error.
    You show us neither the code that fails nor the actual error, yet expect us to tell you how to fix this unseen code.
    Why did you waste your time & our time here with this nonsense?

    My car has an error.
    Tell me how to make my car go.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Dec 2003
    Posts
    76
    Ok, ok .. I got it.

    My error was the following:
    "ORA-02091: transaction rolled back
    ORA-02291: Integrity constraint (xxx.xpto) violated - parent key not found"

    I know what this error is ... and now, after a few tests I've found out WHEN it was happening.
    My doubt was if the DEFFERED command was not working and the error was in the Multitable insert or if the DEFFERED command was working and the error was at the COMMIT time.

    Now, after a few tests I've get it out. This error was at COMMIT time and therefore the DEFFERED command was working.

    I'm so sorry if I wasted your time ... it was not my intention as you can imagine.

    Thanks!

Tags for this Thread

Posting Permissions

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