Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6659

The Reference Guide should state how long a SET CONSTRAINTS command is good for

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.11.1.1
    • Fix Version/s: 10.11.1.1
    • Component/s: Documentation
    • Labels:
      None
    • Urgency:
      Normal

      Description

      A SET CONSTRAINTS command changes the state of a constraint only until the transaction ends (or another, overriding SET CONSTRAINTS command is issued). Once the transaction ends, the constraint reverts to the default behavior declared for it at CREATE/ALTER TABLE time. However, this behavior is not clear from the reference material on SET CONSTRAINTS: https://builds.apache.org/job/Derby-docs/lastSuccessfulBuild/artifact/trunk/out/ref/rrefsqljsetconstr.html

      The following script shows this behavior:

      connect 'jdbc:derby:memory:db;create=true' as conn1;
      
      autocommit off;
      
      create table tprim
      (
          keyCol  int primary key
      );
      create table tref
      (
          refCol  int,
          constraint refcon foreign key( refCol ) references tprim( keyCol ) deferrable
      );
      insert into tprim( keyCol ) values ( 1 ), ( 2 ), ( 3 );
      commit;
      
      set constraints refcon deferred;
      
      -- succeeds because we switched to deferred mode for this transaction
      insert into tref( refCol ) values ( 4 );
      rollback;
      
      -- but fails the second time because the end of the previous transaction
      -- reverted the refcon constraint to its default (immediate) mode
      insert into tref( refCol ) values ( 4 );
      

        Attachments

        1. rrefsqljsetconstr.html
          7 kB
          Camilla Haase
        2. DERBY-6659.diff
          0.9 kB
          Camilla Haase

          Activity

            People

            • Assignee:
              chaase3 Camilla Haase
              Reporter:
              rhillegas Richard N. Hillegas
            • Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: