1. Derby
  2. DERBY-2212

Add "Unique where not null" to create index


    • Type: Improvement Improvement
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s:
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:


      Derby prohibits creation of unique constraints on nullable colums (as well if only some columns in the constraint list are nullable) and treat nulls in unique indexes as normal values (i.e. only one row with null values in indexed columns may be inserted into the table). This bahavior is very restrictive, does not completely comply with SQL standards (both letter and intent) as well as with business needs and intending meaning of NULL values (2 null values are not considered as equal, this comparision shall return NULL, and for selection criteria boolean null is treated as FALSE).

      This behavior, as far as I can see, is modelled after DB2 (and differs from behavior of most other major databases, like SyBase, Oracle, etc.).

      But even DB2 provide some means to alleviate these restrictions, namely "UNIQUE WHERE NOT NULL" clause for CREATE INDEX statement.

      It will be very good if such "UNIQUE WHERE NOT NULL" clause will be introduced in Derby.

      Oleksandr Alesinskyy

      1. derby-2212preview.diff
        16 kB
        Anurag Shekhar
      2. derby-2212preview2.diff
        24 kB
        Anurag Shekhar
      3. FunctionalSpec.html
        2 kB
        Anurag Shekhar
      4. FunctionlaSpecv2.html
        4 kB
        Anurag Shekhar
      5. FunctionalSpecV3.html
        6 kB
        Anurag Shekhar
      6. FunctionalSpecV3_comment.html
        6 kB
        Mike Matrigali

        Issue Links


          Oleksandr Alesinskyy created issue -
          Anurag Shekhar made changes -
          Field Original Value New Value
          Assignee Anurag Shekhar [ anurag ]
          Anurag Shekhar made changes -
          Attachment derby-2212preview.diff [ 12365804 ]
          Anurag Shekhar made changes -
          Attachment derby-2212preview2.diff [ 12367811 ]
          Anurag Shekhar made changes -
          Attachment FunctionalSpec.html [ 12368232 ]
          Anurag Shekhar made changes -
          Attachment FunctionlaSpecv2.html [ 12368736 ]
          Anurag Shekhar made changes -
          Attachment FunctionalSpecV3.html [ 12369089 ]
          Mike Matrigali made changes -
          Attachment FunctionalSpecV3_comment.html [ 12369901 ]
          Anurag Shekhar made changes -
          Link This issue relates to DERBY-3330 [ DERBY-3330 ]
          Anurag Shekhar made changes -
          Assignee Anurag Shekhar [ anurag ]
          Mamta A. Satoor made changes -
          Labels derby_triage10_10
          Urgency Normal [ 10052 ]
          Gavin made changes -
          Workflow jira [ 12393564 ] Default workflow, editable Closed status [ 12797150 ]


            • Assignee:
              Oleksandr Alesinskyy
            • Votes:
              6 Vote for this issue
              4 Start watching this issue


              • Created: