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

Support deferrable constraints

    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 10.11.1.1
    • SQL
    • Normal

    Description

      In many situations it is desirable to have constraints checking taking place only at transaction commit time, and not before. If e.g. there is a chain of foreign key constraints between tables, insert statements have to be ordered to avoid constraint violations. If foreign key references are circular, the DML has to be split into insert statements and subsequent update statements by the user.

      In other words, with deferred constraints checking, life is much easier for the user. Also it can create problems with softwares such as object-relational mapping tools that are not prepared for statement ordering and thus depend on deferred constraints checking.

      Attachments

        1. deferredConstraints.html
          23 kB
          Dag H. Wanvik
        2. deferredConstraints.html
          22 kB
          Dag H. Wanvik
        3. deferredConstraints.html
          22 kB
          Dag H. Wanvik
        4. deferredConstraints.html
          22 kB
          Dag H. Wanvik
        5. derby-532-metadata-queries.diff
          5 kB
          Dag H. Wanvik
        6. derby-532-metadata-queries.status
          0.6 kB
          Dag H. Wanvik
        7. derby-532-fk-7.diff
          206 kB
          Dag H. Wanvik
        8. derby-532-fk-7.stat
          2 kB
          Dag H. Wanvik
        9. derby-532-fk-6.stat
          2 kB
          Dag H. Wanvik
        10. derby-532-fk-6.diff
          205 kB
          Dag H. Wanvik
        11. derby-532-fk-5.stat
          2 kB
          Dag H. Wanvik
        12. derby-532-fk-5.diff
          199 kB
          Dag H. Wanvik
        13. derby-532-fk-4.diff
          192 kB
          Dag H. Wanvik
        14. derby-532-fk-3.stat
          2 kB
          Dag H. Wanvik
        15. derby-532-fk-3.diff
          139 kB
          Dag H. Wanvik
        16. derby-532-fk-1.diff
          111 kB
          Dag H. Wanvik
        17. derby-532-fk-baseline-2.diff
          23 kB
          Dag H. Wanvik
        18. derby-532-fk-baseline.diff
          3 kB
          Dag H. Wanvik
        19. derby-532-check-constraints-2.stat
          5 kB
          Dag H. Wanvik
        20. derby-532-check-constraints-2.diff
          288 kB
          Dag H. Wanvik
        21. derby-532-check-constraints-1.stat
          5 kB
          Dag H. Wanvik
        22. derby-532-check-constraints-1.diff
          283 kB
          Dag H. Wanvik
        23. derby-532-upgrade-1b.diff
          5 kB
          Dag H. Wanvik
        24. derby-532-upgrade-1.status
          0.3 kB
          Dag H. Wanvik
        25. derby-532-upgrade-1.diff
          4 kB
          Dag H. Wanvik
        26. derby-532-allow-pk-unique-1.diff
          21 kB
          Dag H. Wanvik
        27. derby-532-allow-pk-unique-1.status
          0.6 kB
          Dag H. Wanvik
        28. derby-532-test-speedup.status
          1.0 kB
          Dag H. Wanvik
        29. derby-532-test-speedup.diff
          22 kB
          Dag H. Wanvik
        30. derby-532-nullableUniqueFix.status
          0.7 kB
          Dag H. Wanvik
        31. derby-532-nullableUniqueFix.diff
          10 kB
          Dag H. Wanvik
        32. derby-532-fix-drop-not-nullable.status
          0.5 kB
          Dag H. Wanvik
        33. derby-532-fix-drop-not-nullable.diff
          4 kB
          Dag H. Wanvik
        34. derby-532-fix-metadata-1.status
          0.4 kB
          Dag H. Wanvik
        35. derby-532-fix-metadata-1.diff
          10 kB
          Dag H. Wanvik
        36. derby-532-test-with-default-deferrable-all-over.diff
          5 kB
          Dag H. Wanvik
        37. deferredConstraints.html
          18 kB
          Dag H. Wanvik
        38. derby-532-post-scan-4.diff
          188 kB
          Dag H. Wanvik
        39. derby-532-post-scan-4.stat
          4 kB
          Dag H. Wanvik
        40. derby-532-post-scan-3.diff
          185 kB
          Dag H. Wanvik
        41. SortObserver.html
          20 kB
          Dag H. Wanvik
        42. derby-532-post-scan-3.stat
          4 kB
          Dag H. Wanvik
        43. IndexDescriptor.html
          20 kB
          Dag H. Wanvik
        44. IndexDescriptorImpl.html
          44 kB
          Dag H. Wanvik
        45. IndexRowGenerator.html
          53 kB
          Dag H. Wanvik
        46. derby-532-post-scan-2.stat
          4 kB
          Dag H. Wanvik
        47. derby-532-post-scan-2.diff
          184 kB
          Dag H. Wanvik
        48. derby-532-post-scan-1.stat
          4 kB
          Dag H. Wanvik
        49. derby-532-post-scan-1.diff
          179 kB
          Dag H. Wanvik
        50. derby-532-serializable-scan-2.stat
          4 kB
          Dag H. Wanvik
        51. derby-532-serializable-scan-2.diff
          186 kB
          Dag H. Wanvik
        52. derby-532-serializable-scan-1.diff
          223 kB
          Dag H. Wanvik
        53. derby-532-more-tests-1.stat
          0.5 kB
          Dag H. Wanvik
        54. derby-532-more-tests-1.diff
          9 kB
          Dag H. Wanvik
        55. derby-532-unique-pk-3.status
          0.1 kB
          Dag H. Wanvik
        56. derby-532-xa-3.status
          1.0 kB
          Dag H. Wanvik
        57. derby-532-xa-3.diff
          19 kB
          Dag H. Wanvik
        58. derby-532-import-3.diff
          21 kB
          Dag H. Wanvik
        59. derby-532-import-3.status
          0.9 kB
          Dag H. Wanvik
        60. derby-532-unique-pk-3.diff
          223 kB
          Dag H. Wanvik
        61. derby-532-import-2.diff
          22 kB
          Dag H. Wanvik
        62. derby-532-import-1.status
          1.0 kB
          Dag H. Wanvik
        63. derby-532-import-1.diff
          22 kB
          Dag H. Wanvik
        64. derby-532-xa-2.diff
          19 kB
          Dag H. Wanvik
        65. deferredConstraints.html
          19 kB
          Dag H. Wanvik
        66. derby-532-xa-1.diff
          17 kB
          Dag H. Wanvik
        67. derby-532-unique-pk-2.diff
          222 kB
          Dag H. Wanvik
        68. deferredConstraints.html
          18 kB
          Dag H. Wanvik
        69. derby-532-unique-pk-1.status
          6 kB
          Dag H. Wanvik
        70. derby-532-unique-pk-1.diff
          222 kB
          Dag H. Wanvik
        71. derby-532-testAlterConstraintInvalidation.status
          0.4 kB
          Dag H. Wanvik
        72. derby-532-testAlterConstraintInvalidation.diff
          4 kB
          Dag H. Wanvik
        73. derby-532-syntax-binding-dict-all-1.diff
          118 kB
          Dag H. Wanvik
        74. derby-532-syntax-binding-dict-2.status
          2 kB
          Dag H. Wanvik
        75. derby-532-syntax-binding-dict-2.diff
          57 kB
          Dag H. Wanvik
        76. derby-532-syntax-binding-dict-1.status
          3 kB
          Dag H. Wanvik
        77. derby-532-syntax-binding-dict-1.diff
          89 kB
          Dag H. Wanvik
        78. deferredConstraints.html
          16 kB
          Dag H. Wanvik
        79. deferredConstraints.html
          15 kB
          Dag H. Wanvik

        Issue Links

          incorporates

          Bug - A problem which impairs or prevents the functions of the product. DERBY-6374 Bulk insert of data with nullable UNIQUE constraint fails to detect duplicates

          • Major - Major loss of function.
          • Closed
          is broken by

          Bug - A problem which impairs or prevents the functions of the product. DERBY-6559 A immediate Fk constraint blows up iff its referenced PK is deferred and we delete a duplicate

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. DERBY-6576 A immediate Fk constraint blows up iff its referenced PK is deferred and we modify a duplicate key column

          • Major - Major loss of function.
          • Closed
          is depended upon by

          Sub-task - The sub-task of the issue DERBY-6571 Document deferrable constraints

          • Major - Major loss of function.
          • Closed
          is related to

          Bug - A problem which impairs or prevents the functions of the product. DERBY-6423 The expression syntax in CASE's THEN clause doesn't accept boolean value expression

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. DERBY-6661 dblook does not recognize the deferrability of deferrable constraints

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. DERBY-6668 Truncating a table may silently violate a deferred foreign key.

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. DERBY-6670 Rollback to savepoint allows violation of deferrable constraints

          • Major - Major loss of function.
          • Closed

          Improvement - An improvement or enhancement to an existing feature or task. DERBY-6672 Allow Derby to rename tables referenced by foreign keys

          • Major - Major loss of function.
          • Closed

          Improvement - An improvement or enhancement to an existing feature or task. DERBY-6674 Cleanup brittle code in ValidateCheckConstraintResultSet

          • Major - Major loss of function.
          • Closed

          Improvement - An improvement or enhancement to an existing feature or task. DERBY-6453 Remove dead code in InsertResultSet and flag skipCheckConstraints

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed
          relates to

          Improvement - An improvement or enhancement to an existing feature or task. DERBY-6303 Add ability to defer enforcement of unique indexes until transaction end.

          • Major - Major loss of function.
          • Open

          Activity

            People

              dagw Dag H. Wanvik
              jfrantzius Jörg von Frantzius
              Votes:
              6 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: